AnsweredAssumed Answered

Calculating if date is summer time

Question asked by nRogerGouv on Oct 1, 2018

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

Outcomes