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.                                                        

Answer
Discuss

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: 1492) 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: 2205) 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: 1492) Aug 9, '17 at 5:20 am
Add to Discussion

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:

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

Discuss

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: 1492) Aug 9, '17 at 2:57 am
Add to Discussion
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

Discuss

Discussion

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: 1492) Aug 10, '17 at 7:12 am
Add to Discussion

Answer the Question

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