How to Calculate a Target Date AND Time excluding holidays, weekends, and off-work times


Hello All,

I have searched and searched and cannot seem to find the answer to my question.  I have a Start date and time in cell B5 in the format of:  8/29/2017 9:00 AM.  My target is 48 hours after the Start date and time.  I want cell F5 to automatically populate with the target date and time after I enter the start date and time in B5. 

Here are the specifics:

Working hours are 6:00 AM - 5:00 PM

Holidays are in a list on a different tab.

Weekends should be excluded.

Any help would be greatly appreciated!





Check out the WORKDAY function in excel.  The online help as well as google has all the infor you will need.  The best way to get command of this is to look it up and see exactly what you're looking for (this does have a holiday exclusion!!)

Please attach a sample spreadsheet (i'm not sure how you are formatting cells).  I put your date in B5 (the decimal value is 42,976.38) and iin F5 i added 2 to it (formula= +b5+2) and it works.  The reason to add 2 is that the nondecimal part of the number in cell b5 is 42976....excel uses the decimal part for hours in the day, so 12 pm is = .5 (12 hours divided by 24 hours in a day; 9am is 9/24 = .375  ~ I hope this helps you



Here's a snip of my spreadsheet.  Column B (which is the start date and time) is the only one that matters for this discussion.  I want each row in column F to show a target date and time of 48 hours after the date and time in column B (formatted just like column B also).  I have defined the holidays as a named list on a different tab. I hope this helps to explain what I'm looking for.  Thank you!!!!

sspost Sep 5, '17 at 4:09 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login