Using Alteryx to retrieve timezone information from Excel times - Is this even possible?

[MarkMcCullagh]

Hi

I have a task to reconcile two sets of financial exchange data - one external which seems to be on UST/GMT and another one which may be UST or day light saving time or from somewhere else in the world. I want to be able to see items where the data does not match adjusted for timezones.

I'm getting the data in Excel formats (Side A times are labelled GMT, Side B I have no labelling as yet in my input file).

13:00 GMT = 14:00 BST

but 13:00 GMT <> 14:15 BST

nor 13:00 GMT <> 13:13 GMT

Does anyone know if Excel stores down timezone information or does it merely store the local time?

If Excel does store down timezone information how would I ascertain this using Alteryx?

Thanks

Mark

[jarrod · Accepted solution]

Alteryx only knows the machine time and how to convert to UTC. you'll have to build logic around what you can gather from the incoming files, but one way is to convert to UTC then create the <> filter.

here are some helpful formulas that i've used to convert times across timezones:

DateTimeToLocal(dt)

DateTimeToUTC(dt)

DateTimeStart(dt)

DateTimeAdd(dt,i,u)

[danilang · Accepted solution]

Hi @MarkMcCullagh 

Excel doesn't store time zone information in the standard datetime fields.  All dates are assumed to be in the time zone of the person using the file.  Any info with a time zone descriptor is a text field.  Unless you can find out the timezone of the place where you got the 2nd excel file, there's no way to determine the offset.

Dan