Get Local Timezones based on CET time in the report

Question

Dear colleagues,

I have below sample of report which includes dates in CET timezones (PL timezones) for the CEE countries and offices. I need to get local timezone for each country/date, taking into account time changes/shifts during the year. I need this to be working for past and coming years as well (so timeshift build into workflow).

Timezones sample.xlsx

joannasokolowska · Answer

Hi,

I started creating the workflow but I got stuck so I appreciate your help.

1. The time shift is always happeing the last Sunday of March (into summer time)  and last Sunday of October (into winter time) so not sure how to calculate it for all years.

2. The offset is with GTM in the timezones summary file while datetime in timezones file is in CET time (the same as for Poland) , so how can I get local time not GMT?

I am attaching part of the workflow I started working on.

timezones.yxmd

alexnajm · Answer

I can, but I think you can try it out too! A few things you'll need:

- parse out the offset (+2, +3, etc.) and make it a number so you can do DateTimeDiff calcs

- note down dates that are considered "summer" versus "winter" - is it first half versus second half of the year, or something else?

- Join on country so the data links together

- make sure your date column is in a true datetime format for calculations

Once you have tried it, feel free to come back with a workflow and questions and we can help from there. Good luck!

joannasokolowska · Answer

Hi,

Thank you for reaching out me so quickly! I am attaching additional file with timezones - can you help using these 2 files?

Best,

Asia

Timezones summary.xlsx