How to make sure a follow up date falls on a weekday in Excel

Subscriber Theresa M. asks: ‘We send out letters and follow up 45 days after the initial letter. We have a formula to calculate the dates but the dates need to be on a business day. Is there is a formula in Excel that can do that for you?’

 

There isn’t a formula to change the date to a weekday but you can use Excel’s WEEKDAY function to figure out the day of the week the follow up date falls on and change it if needed.

 

The WEEKDAY function returns a 1 if the date is a Sunday, 2 if the date is a Monday, etc.

 

So the spreadsheet would initially look like this:
ba22264a-8314-40ef-a4b8-07889f29bf19

Cell D3 contains 6-Apr
Cell E3 contains =D3+45
Cell F3 contains =WEEKDAY(E3)
Cell G3 contains =IF(F3=7,E3+2,E3)

If the date is a Saturday, the IF function adds 2 to the date thus changing it to a Monday.

 

You could have a second IF function to check for dates that were a Sunday. Once you have everything working correctly you could use copy & paste to reduce the number of cells needed and do all the calculations in one cell. If you need help with this let me know and I’ll send you a sample spreadsheet.

 

Excel also has the following date functions:
=DAY(date) – the day of the month
=MONTH(date) – the month (1 = Jan, 2 = Feb, etc.)
=YEAR(date) – the year