Have cell display Monday/Friday date if a date falls on the weekend

**tcdccinc** · 06-13-2022, 01:15 PM

Hello,

I'm working on a yearly calendar. I currently enter the start year, month, and day of the week so that a calendar can autofill with dates.

I have listed days that my job is closed and those dates get highlighted on the calendar using conditional formatting.

For dates like holidays, I use a formula to auto-update the date every year. However, when a holiday falls on a Saturday my job observes it on the Friday before, and when a holiday falls on a Sunday it is observed on the following Monday.

In the past I have just manually changed these dates but is there a way to format the formulas so that the date updates automatically?

Any help or suggestions would be greatly appreciated.

**TMS** · 06-13-2022, 02:45 PM

If the holiday dates are in a separate list, you could apply a formula to that list. Check if the holiday dates are Saturday or Sunday using the weekday function to create your new list. Then use the new list for your C rather than the original.

**6StringJazzer** · 06-13-2022, 02:48 PM

This formula will do it, where G3 is the date:

Formula:

Please Login or Register  to view this content.

Just for reference I have attached my holiday calendar to show how I calculate dates for several holidays over several years. Using your method you still have to manually enter a holiday date (like 1/1/2022). My company had the day after Thanksgiving as a holiday, which is especially quirky to calculate.