Calculating if date is summer time

114
2
10-01-2018 12:25 PM
Highlighted
Occasional Contributor

Hi,

I have an excel formula which tell me if a date is using summer daylight saving time or not. The formula is as follow and seems to work well in excel:

=MATCH(A2,DATE(YEAR(A2),{1,3,11},{1,15,8})-WEEKDAY(DATE(YEAR(A2),{1,3,11},7)))=2

It returns true or false. I would like to implement to formula in my Survey123 form but so far I can't make it to work properly (or at all).

Let's say the survey have a Date field named "aDate". I tried changing the formula like so:

=MATCH(${aDate},DATE(YEAR(${aDate}),{1,3,11},{1,15,8})-WEEKDAY(DATE(YEAR(${aDate}),{1,3,11},7)))=2

But it seems it doesn't work. Any help would be greatly appreciated.

To be clear, I know that we can get the current UTC offset of the device, but I would like to get the UTC offset of a date that the user selected (So that I can tell if it is summer time or not.)

SOLUTION: Ok I found a way to do it. Basicly, I implemented the algorithm found here (for US-Canada East time). To implement it. I had to use several Calculate question in a row. The basic pattern I followed was:

-Find weekday abreviation using:

format-date(${dateField},'%a')‍

-Convert the weekday to a number between 0 and 6 using something similar to this (adapt according to your language):

if(${dayOfWeek}='dim',0,if(${dayOfWeek}='lun',1,if(${dayOfWeek}='mar',2,if(${dayOfWeek}='mer',3,if(${dayOfWeek}='jeu',4,if(${dayOfWeek}='ven',5,if(${dayOfWeek}='sam',6,99)))))))‍

-Implement the algorithm using format-date(${dateObs},'%n') for months and format-date(${dateObs},'%e') for Day of month

2 Replies
Highlighted
Esri Esteemed Contributor

Hi Nicolas,

The formula you have doesn't translate directly into a formula that can be implemented in Survey123; while the formatting document is Excel, it does not implement Excel functions.  

Another formula would need to be created to check if the current date is within the DST timeframe (which varies by country).  Using the format-date() function, it could be possible to construct but would be rather complex.

Reply
0 Kudos
Highlighted
Occasional Contributor

Thank you for your help. I will report back here if I manage to do it.

Reply
0 Kudos