##### 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.)

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

1

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.

### Discussion

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

0

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

``````=WORKDAY(A2,INT(B2/8)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>
\$F\$2,1,0))+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>\$F\$2,\$E\$2
+TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)-\$F\$2,TIME(HOUR(A2),MINUTE(A2),SECOND(A2))
+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0))``````

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.)

### Discussion

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
Lian Tran Oct 3, '20 at 12:36 am
0

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

Thank you very much for your help

### Discussion

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
@classic80srock:

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