Please Help

[Ross Jones]

I have an excel formula to determine if a given date (in cell C) is within or without DST.

Here is formula :

=IF(MATCH(C8,DATE(YEAR(C8),{1,3,11},{1,15,8})-WEEKDAY(DATE(YEAR(C8),{1,3,11},7)))=2,"DST","ST")

This works in excel, but in sheets it always evaluates to ST. Can anyone help me get this working in sheets?

Thanx!!

[Hassam Saeed]

Hey Ross,

Please check out the solution in the attached image.

Formula used:

=IF( AND( DATEVALUE(B2) >= DATE(YEAR(B2), 3, 14-WEEKDAY(DATE(YEAR(B2), 3, 14))), DATEVALUE(B2) < DATE(YEAR(B2), 11, 7-WEEKDAY(DATE(YEAR(B2), 11, 7))) ), "DST", "ST" )

[Ross Jones]

My formula:

In Excel, 11/2/2024 evaluates to DST, 11/3/2024 evaluates to ST. This is correct

In Sheets they both evaluate to ST which is not correct

Hassam Saeed formula:

in Excel a message says this is not a formula

In sheets it evaluates incorrectly,.

e.g.  10/1/2024 evaluates to DST-correct

11/2/2024 evaluates to ST-not correct

11/3/2024 evaluates to ST-correct.

In other words it has the change from DST to ST a day earlier than it really is-didn't check the march DST start date

Also, this formula doesn't seem to work at all for 2025

Don't understand why it would evaluate differently in Excdl and Sheets?

[Ross Jones]

Found a formula that seems to work

=IF(A12=0,"", IF(AND(D12>=DATE(YEAR(D12),3,1)+14-WEEKDAY(DATE(YEAR(D12),3,1)-1), D12 < DATE(YEAR(D12),11,1)+7-WEEKDAY(DATE(YEAR(D12),11,1)-1)),"DST","ST"))