10 Replies Latest reply on Nov 29, 2012 2:17 PM by Shawn Wallwork

# daylight savings time

I'm sure one of you datagurus can do this easy, but My mind hurts.

I have a great formula to convert Central daylight time and central standard time into GMT.

=if(and(A2>date(year(A2),4,1-weekday(date(year(A2),4,0)))+1/24,A2<date(year(A2),11,1-weekday(date(year(A2),11,0)))+1/24),5,6)/24+A2

but I'm having trouble getting a good tableau formula.

I can't use AND

I can't use BETWEEN

I hope someone out there has ideas.

thanks

• ###### 1. Re: daylight savings time

if [value]>4 and [value]<8 then ksdfbgskdjfhsk end

• ###### 2. Re: daylight savings time

Maybe something like this?

I dont know when daylight savings time (DST) starts and ends...

I think tableau keeps datetime records as one dimension...

Converted GMT Time:

if DateTime_Variable< DST_START AND Date_Variable > DST_END then dateadd('hour',5,[DateTime_Variable])

else DateTime_Variable>= DST_START AND Date_Variable <= DST_END then dateadd('hour',6,[DateTime_Variable])

end

?

• ###### 3. Re: daylight savings time

You don't need year in there - I believe this works:

if (datepart('day',[Order Date])-datepart('weekday',[Order Date])<7 and datepart('month',[Order Date])=3)

or (datepart('day',[Order Date])-datepart('weekday',[Order Date])>=0 and datepart('month',[Order Date])=11)

or datepart('month',[Order Date])<3 or datepart('month',[Order Date])>11

then "No" else "Yes" end

No could be replaced with 6/24 or 5/24 for Yes if you are using fractional times, then add this to your time (or datetime). This does not account for the fact that the actual time of change is 2AM Sunday, and won't work historically of course.

• ###### 4. Re: daylight savings time

Attached

• ###### 5. Re: daylight savings time

My post was a joke and i didn't think it would be taken the wrong way but I apologize if anyone took it the wrong way.

• ###### 6. Re: daylight savings time

Hub, thanks for both clarifying, and taking it down. I really do appreciate it.

--Shawn

Edit: Weird hearing myself say these words because I'm so NOT a PC guy. Oh well maybe just a mood.

Message was edited by: Shawn Wallwork

• ###### 7. Re: daylight savings time

Not at all Hub. I didn't read it that way at all. I was actually interested in trying to get the logic for the actual DST move - weekdays and dates still confuse me.

• ###### 8. Re: daylight savings time

Shawn, I would guess it was having to listen to my accent on today's TDT call....

• ###### 9. Re: daylight savings time

Actually its probably having to use a PC when using tableau. hope a mac version comes out soon for Shawn.

• ###### 10. Re: daylight savings time

Must say this totally threw me! I'm thinking American, and you show up to TDT with that wonderful non-Yank accent...suddenly I'm in a conversation with the characters from the 'Hour'!

Tableau, you do bring the world together!

--Shawn