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

Calculation of end date by using start date and time and working hours as inputs excluding weekends in excel


  Here incident is created at particular date and time .We set some priorties          priority  severity resolve issue in time                   p1 s1 4 hrs                   p2 s2 9hrs                   p3 s3 27hrs                   p4 s4 45hrs                                                                         Now we need to calculate closure date by excluding saturdays and sundays         and timings 9am to 6 pm.                   if incident created on Friday evening 6 we need to calculate hrs from Monday 9 am because of weekends.   Pls solve this.                                                        



Please don't forget to select the answer that worked best for you! Just click the Select Answer button at the bottom of the desired answer.
don (rep: 1989) Sep 2, '16 at 12:07 pm
The formula in the answer below demonstrates perfectly why I wouldn't want to have it in my worksheet: an expert would take many hours to modify it. I would solve the problem with VBA. That would also require an expert to modify but it would take just minutes because what it does is easy to read. I would have submitted a VBA solution but your requirement isn't clearly stated.
Variatus (rep: 4889) Aug 9, '17 at 4:53 am
Thankfully, I found that formula on the web and didn't make it from scratch; otherwise, I would have gone crazy haha
don (rep: 1989) Aug 9, '17 at 5:20 am
Add to Discussion


Selected Answer

You are in luck! There is a great formula for this.


In this example it is assumed that you have 4 cells setup like this:

  1. A2 is the starting date/time
  2. B2 is the number of hours
  3. E2 is the starting time of the workday
  4. F2 is the ending time of the workday

You can change the cell references as you need but be careful since it's a huge formula and you will need to change things in a few different places.

(I found this formula on the web a while back and it has worked great for me.)



How can I exclude Weekends (i.e. Saturday & Sunday) in the above formula
Dnyan Aug 8, '17 at 11:43 am
The WORKDAY() function used in the formula should already take care of this.
don (rep: 1989) Aug 9, '17 at 2:57 am
If I have 2 time range to working 
8:00 to 12:00
13:00 to 17:00
So, what I can use formula? Please help me. Thank you
Lian Tran Oct 3, '20 at 12:36 am
Add to Discussion

yes, thank you very much, I wrongly entered different year, thats why I was not getting answer.

Thank you very much for your help



I'm glad it worked!

Next time, please make posts like this in the Discussion section and not in the Answer section of a question.
don (rep: 1989) Aug 10, '17 at 7:12 am
I too have found this code and it work great for calculating the End Date/Time of a project.  How would the code look to use the End Date and Hours projected to calculate what the Start Date/Time should be?
classic80srock Apr 11, '23 at 6:17 pm

Check your post of April 12 (Your forum post) and my file "Calculate Start Date and Time V1" for the answer.

Cheers   :-)
WillieD24 (rep: 547) Apr 21, '23 at 10:26 pm
Add to Discussion

Answer the Question

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