Formula for determining whether a date occurred during daylight savings or standard time

Liesik

I've got a sheet that I'm using to track the go live dates of items for a project I'm working on. These always go live at 10:00am in my local time (PST/PDT). The go live timestamps for these items need to be entered in UTC on the backend, when means the times need to be entered as 18:00 if the item's go live date is during standard time or 17:00 if the item's go live date is during daylight savings.

I'd like to reduce the opportunity for human error by adding a column with a formula that increases the visibility of dates that fall within the standard time window versus dates that fall within the daylight savings window. I've been able to do this in Excel using the formula below, but haven't had any success in translating it to Smartsheet.

=IF(MATCH(A2,DATE(YEAR(A2),{1,3,11},{1,15,8})-WEEKDAY((DATE(YEAR(A2),{1,3,11},7))))=2,"17:00","18:00")

Ultimately, I'd like to find a formula that's flexible enough that it doesn't need to be modified constantly, and that doesn't require me to reference a completely separate, manually maintained sheet full of dates.

Austin Smith

@Liesik

I mean... this would be the most permanent method to solve your problem.

https://www.change.org/p/eliminate-daylight-savings-time

Didn't the excel formula require that you maintain it, and doesn't that impose enough human error to be a problem? What happens if you forget or leave?

I know you said you don't want one, but I'd build a reference sheet with all future change dates on it and use a year and month reference to filter. Seems clunkier than a short formula, but it's less prone to break or need an update, outside of adding new dates every ten years. By the time you reach the end of the list, your company likely won't be using smartsheet, and/or you won't be the person that has to update the sheet.

https://www.calendar-365.com/holidays/daylight-saving-%28end%29.html