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.

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

1

### 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: 1969) 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: 4864) 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: 1969) Aug 9, '17 at 5:20 am

## Answers

0

Selected Answer

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:

- A2 is the starting date/time
- B2 is the number of hours
- E2 is the starting time of the workday
- 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: 1969) 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

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

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

I'm glad it worked!

Next time, please make posts like this in the

Next time, please make posts like this in the

**Discussion**section and not in the**Answer**section of a question.
don (rep: 1969) Aug 10, '17 at 7:12 am