Attendance Calendar - Holidays that fall on Weekends, show nearest weekday

[merno]

Hello,
I have an attendance calendar that automatically tells the date of holidays based on the year in cell: G2. I need the holidays that fall on Saturday to show the date as the Friday before, and holidays that fall on Sunday to show the Monday after.

Here is the equation I have for Christmas Day 2016:

=DATE(G2,12,31)

In this sheet, G2 cell: 2016

For Christmas Day 2016 it falls on a Sunday, so I want it to give me the date: December 26, 2016

What do I need to add the equation to show this?

 

[Eric W]

Maybe:

=DATE(G2,12,25)+CHOOSE(WEEKDAY(DATE(G2,12,25)),1,0,0,0,0,0,-1)

This will add a day if Christmas falls on a Sunday, and subtracts a day if it falls on Saturday.

 

[mole999]

what happens to boxing day on a saturday

 

[merno]

This works! Thank you!

 

[Eric W]

You're welcome!