r/excel•
diceone

Calculate a Calendar Days but result be last Business Day if it falls on a Weekend/Holiday

Waiting on OP

I am working on a solution to automate due dates for team tasks since they often put in weekend/holiday dates on accident.

For example: I have a task that needs to be completed in 21 calendar days and is starting October 22nd 2018, it would land on November 12th which is Veteran's Day here in the states. I'd want it to reflect that it needs to be completed on November 9th which is the last business day before the date due.

Another example would be 5 calendar days, say it was started Sept 5th 2018. 5 days (including the date started) would be Sept 9th, so I'd want the due date from the formula to be Sept 7th, 2018 (the last business day before that Sunday).

Is something like this even possible? I was trying to make a reference sheet of all holidays but the varying # of dates (ie. -1 or -2 days) made it difficult.

Posted Sep 6, 2018, 4:09 PM UTC

22
finickyone222•

Yes all possible:

=WORKDAY(enddate+1,-1,holidays)

Enddate you need to supply, recommend pointing this to a cell. Holidays needs to be a list you provide somewhere (just the dates). Discounts weekends regardless.

Edit - got bracket happy.

1
finickyone222•

Another example would be 5 calendar days

=WORKDAY(startdate + suggested no of days+1,-1,holidays)

Edit: lost count of three brackets..

1