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
