# 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: 1835) 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: 3928) 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: 1835) 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: 1835) Aug 9, '17 at 2:57 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