Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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

0

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!

Thanks!

excel
MS Office Version: 2010
posted by: sspost on Tue Sep 5, 2017 at 3:14 pm
Answer
Discuss

Answers

0

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

posted by: queue (rep: 467) on Tue Sep 5, 2017 at 3:50 pm
Discuss

Discussion

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