Tableau UTC Conversion

In one of my earlier posts, I wrote about how to manually convert UTC time to Eastern time, adjusting for the Standard vs Daylight Savings. I’ve found this formula useful for some of SQL work but recently needed to incorporate it into Tableau.

So I decided to share the following calculated field. Simply cut and paste the formula below into your favorite text editor and Replace All the [date_field] with your date column name.

if date([date_field]) > #13-03-2011# and date([date_field]) < #06-11-2011# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #11-03-2012# and date([date_field]) < #04-11-2012# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #10-03-2013# and date([date_field]) < #03-11-2013# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #09-03-2014# and date([date_field]) < #02-11-2014# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #08-03-2015# and date([date_field]) < #01-11-2015# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #13-03-2016# and date([date_field]) < #06-11-2016# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #12-03-2017# and date([date_field]) < #05-11-2017# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #11-03-2018# and date([date_field]) < #04-11-2018# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #10-03-2019# and date([date_field]) < #03-11-2019# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #08-03-2020# and date([date_field]) < #01-11-2020# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #14-03-2021# and date([date_field]) < #07-11-2021# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #13-03-2022# and date([date_field]) < #06-11-2022# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #12-03-2023# and date([date_field]) < #05-11-2023# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #10-03-2024# and date([date_field]) < #03-11-2024# then DATEADD(‘hour’,-4,[date_field])
elseif date([date_field]) > #09-03-2025# and date([date_field]) < #02-11-2025# then DATEADD(‘hour’,-4,[date_field])
else DATEADD(‘hour’,-5,[date_field]) end

Please note, this converts UTC to US Eastern time zone. So if you’re in another time zone, be sure to adjust the DATEADD values. I hope this helps.

Share Button

9 comments

  1. Thanks, worked great. Only issue… when I copied/pasted, the single quote was unrecognized by Tableau so I had to find/replace, but that was easy. Thanks!

    1. Interesting. I just tested and got the same result as you did. But if pasted into Notepad++ first, then into Tableau, it recognizes the single quote correctly. Thanks for the heads up and glad you could use it!

  2. If you’d like a more elegant solution that scales to any date, you can use this:

    IF date([date_field]) >= DATETIME(STR(YEAR(date([date_field]))) + ‘-03-‘ + STR(IF DATEPART(‘weekday’, DATE(STR(YEAR(date([date_field]))) + ‘-03-01’)) = 1 THEN 9 ELSE 16 END – DATEPART(‘weekday’, DATE(STR(YEAR(date([date_field]))) + ‘-03-01’))) + ‘ 07:00:00’) AND
    date([date_field]) < DATETIME(STR(YEAR(date([date_field]))) + '-11-' + STR(IF DATEPART('weekday', DATE(STR(YEAR(date([date_field]))) + '-11-01')) = 1 THEN 2 ELSE 9 END – DATEPART('weekday', DATE(STR(YEAR(date([date_field]))) + '-11-01'))) + ' 06:00:00') THEN DATEADD('hour', -4, date([date_field])) ELSE DATEADD('hour', -5, date([date_field])) END

    1. Thanks Jonathan its much useful. Can you please help me understand why we using THEN 9 ELSE 16 and THEN 2 ELSE 9?. what those number meant for?
      Thanks!!

Leave a Reply