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.
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
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
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: 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
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 Discussion section and not in the Answer section of a question.
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 :-)
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