Check daylight saving time in calc

william.in.rs · April 18, 2024, 7:09pm

I have to do some date/time calculations which depend on DST (daylight savings time). Can I check whether a date in a cell CellRef belongs to DST using formula:

AND(ISOWEEKNUM(CellRef+1)>13;ISOWEEKNUM(CellRef+1)<43)

ignoring errors for times between midnight and 3 AM on Sundays when the DST changes occur?

fpy · April 18, 2024, 8:15pm

maybe a more robust approach :
https://help.libreoffice.org/latest/en-US/text/sbasic/shared/03/sf_region.html

william.in.rs · April 23, 2024, 9:22am

Thank you for pointing to a complete solution, however I was wondering whether my approach is correct enough to eliminate one hour difference between CET and CEST in most cases. I’d like some comments about the formula.

fpy · April 23, 2024, 2:54pm

well, yeah, if it doesn’t matter to you the year the change are not week 13 or 43
but the correct are last Sunday of March and October, aren’t they ?

william.in.rs · April 23, 2024, 7:28pm

Thanks again!

At a first glance I thought that last Sunday of March and October should fall into same ISOWEEKNUM, as it starts always on Monday, and that was the reason why I posted the question.

Later check showed that this is almost true for the end of DST period. In the years 2000 till 2030 all last Sundays of October are in the ISOWEEKNUM of 43. There is only one exception in year 2004 with ISOWEEKNUM of 44.

ISOWEEKNUM for the last Sunday of March depends on WEEKDAY of January 1st. If WEEKDAY(DATE(YEAR(CellRef);1;1);12) is less than 4 then ISOWEEKNUM is 13, being 12 otherwise. There is one exception again. WEEKDAY(DATE(2024;1;1);12) is 7 which is not less than 4, but ISOWEEKNUM(DATE(2024;3;31)) is 13 and not 12.

This means that the corrected formula:

AND(ISOWEEKNUM(CellRef+1)>
IF(WEEKDAY(DATE(YEAR(CellRef);1;1);12)<4; 13; 12);
ISOWEEKNUM(CellRef+1)<44)

could be a good approximation of Date_Is_In_DST(CellRef) function in the interval of years 2000 to 2030, with error for dates between October 24th to 31st 2004 and March 24th to 31st 2024.

fpy · April 24, 2024, 11:26am

good upper bound, cause in 2032 …

I guess it’s also true (or false!) for October, and actually all days in the calendar ?

anyhow, not sure IF is the way.
better rely on EOMONTH()
something like
=EOMONTH(DATE(YEAR(A1),10,1),0)-WEEKDAY(EOMONTH(DATE(YEAR(A1),10,1),0))+1

william.in.rs · May 16, 2024, 8:03pm

Great!
Works like a charm!
Much, much better than any of my “solutions” .
IsDST(RefCell) could be replaced with:

AND(RefCell>=EOMONTH(DATE(YEAR(RefCell);3;1);0)-WEEKDAY(EOMONTH(DATE(YEAR(RefCell);3;1);0))+1+2/24;
RefCell<EOMONTH(DATE(YEAR(RefCell);10;1);0)-WEEKDAY(EOMONTH(DATE(YEAR(RefCell);10;1);0))+1+2/24
)

Thanks a lot!

karolus · April 23, 2024, 10:49am

~~maybe~~ a much more robust approach:

from datetime import datetime as dt
from zoneinfo import ZoneInfo

naive1 = dt(2024, 3, 31, 2 , 59)
naive2 = dt(2024, 3, 31, 3)

tz_aware1 = naive.astimezone(ZoneInfo("localtime"))
tz_aware2 = naive2.astimezone(ZoneInfo("localtime"))

print(f"{naive1 = :%Y-%m-%d %H:%M:%S %Z}\n"
      f"{tz_aware1 = :%Y-%m-%d %H:%M:%S %Z %z}\n"
      f"{naive2 = :%Y-%m-%d %H:%M:%S %Z}\n"
      f"{tz_aware2 = :%Y-%m-%d %H:%M:%S %Z %z}")

⇒

naive1 = 2024-03-31 02:59:00 
tz_aware1 = 2024-03-31 01:59:00 CET +0100
naive2 = 2024-03-31 03:00:00 
tz_aware2 = 2024-03-31 03:00:00 CEST +0200

https://docs.python.org/3/library/datetime.html#module-datetime