Daylight Savings Problem - Virtual Duration Column

[limbstan]

I have a virtual column which calculates a duration between the current time and the latest previous time.  When the entry is open for edit, the virtual duration column is correct, but when saving the entry, the virtual column reverts to a duration of an hour more than the actual duration.  I have tracked this to an issue with daylight savings, but I can't figure out how to fix it.

[AleksiAlkio]

I'm afraid this is a long time and known bug with daylight saving. A workaround is to add +1 or -1 hour depending on the start or end date.

[MultiTech]

Here's a formula you can use to determine if a date in inside DST or not:

if(
SWITCH(MONTH(NOW()), 
  3, today() >= EOWEEK(date(Concatenate("3/1/", text(today(), "yyyy")))) + 8,
  11, today() < EOWEEK(date(Concatenate("11/1/", text(today(), "yyyy")))) + 1,
  AND(
    MONTH(NOW()) > 3,
    MONTH(NOW()) < 11
  )
), 
  "001:00:00",
  "000:00:00"
)

Here's how it works:

1. SWITCH(MONTH(NOW()), ...): This part of the formula evaluates the current month and executes one of the conditions based on the month:
   
   • For March (`3`): It checks if today's date is greater than or equal to (`>=`) the date of the second Sunday in March. This is calculated by taking the first day of March for the current year (`date(Concatenate("3/1/", text(today(), "yyyy")))`), finding the Saturday of that week with `EOWEEK(...)`, and then adding 8 days to get to the second Sunday.
   • For November (`11`): It checks if today's date is before (`<`) the first Sunday in November. This is done by finding the Saturday of the first week of November and adding 1 day to get the first Sunday.
   • For months between March and November: The `AND(...)` condition checks if the current month is after March and before November. If true, this indicates the period when DST is typically in effect.
2. The `if` condition: Based on the result from the `SWITCH` statement, this conditional logic applies a time offset to adjust the UTC time to Eastern Time, considering DST:
   
   • If DST is in effect (the conditions for March or the months between March and November are true), subtract `"001:00:00"` from the current time to get the DST version.
   • Otherwise, subtract `"000:00:00"` from the current time.

This formula dynamically calculates the start and end dates of DST by using the `EOWEEK` function to find the relevant Sundays in March and November. By concatenating the year from the current date, it ensures the calculation is always based on the current year, making it automatically adjust for future years without needing to manually update the year in the formula.