Correct Calculation for July 4th

[WashC]

hello everyone,

the formula below calculates July 4th but it's not always correct. when it falls on Saturday, we observe it on Friday and when it falls on Sunday, we observe it on Monday.
Independence Day -> =DATE(CalendarYear,7,4)

what's the correct way to do it?

for reference,

2019 Thursday July 4, 2019 Thursday, July 4, 2019
2020 Saturday July 4, 2020 **Friday, July 3, 2020
2021 Sunday July 4, 2021 Monday, July 5, 2021
2022 Monday July 4, 2022 Monday, July 4, 2022

Thank you

[Pete_UK]

With your years in cells A2 to A5, you can use this formula in B2:

=DATE(A2,7,4)+IF(WEEKDAY(DATE(A2,7,4),2)=6,-1,IF(WEEKDAY(DATE(A2,7,4),2)=7,1,0))

then copy it down to B5.

Hope this helps.

Pete

[Ron Coderre]

If your year is in cell A1
try this regular formula:

Please Login or Register  to view this content.

Is that something you can work with?

[WasWodge]

Edit: Deleted as mis-understood Pete's instructions, apologies

[Pete_UK]

Ah yes, I've not used CHOOSE for a long time. A variation on Ron's formula:

=DATE(A2,7,4)+CHOOSE(WEEKDAY(DATE(A2,7,4)),1,0,0,0,0,0,-1)

Hope this helps.

Pete

[Root_]

@ WashC:

See if the following formula works for you:

=WORKDAY(DATE(CalendarYear,7,3)-(WEEKDAY(DATE(CalendarYear,7,4))=7),1)

[FlameRetired]

Another way with years starting in A2

Please Login or Register  to view this content.

[Ron Coderre]

Here's the problem with unusual date calcs in Excel:
The short formulas make no sense....and pretty much all of the others make no sense, too!

[FlameRetired]

LOL

Well said.

[WashC]

Thank you Ron. I think yours is the one that makes the most sense to me!! lol
All you guys got it working correctly. so ....

THANK YOU!!!