Hello,
I'd like to calculate the finishing termin of a process.
I have a given starting date (time) and the duration of the process itself. It takes some time (eg. 3 days and 12 hrs). The problem is, that we have several breaks during the day and probably weekends or official holidays. Is there a possibility to calculate when exactly the process is finished?
Thank you in advance.
Entropie
Just to confirm, are you saying that the entire process could take 84 hours in total to do ie (hypothetically) if one person was to carry out the process non-stop it would take that time to complete but you want to take into account working hours (say 8 hour shifts), weekends etc? ie if that person worked 8 hours per day, 7 days per week then it would take 10.5 days? however they have two 15mins breaks during that 8 hours so that adds another half hour per day on taking it to roughly 11 days? Only work weekdays so that drops 2 days per week etc etc
If someone has helped you then please add to their Reputation
Yes, your example is quite good. The problem I can’t work around is, that It depends on when the process is started, if more or less weekends / breaks have to be taken into account. Even worse if there are banking holidays they have to be considered too (so I need a holiday calendar).
All I want to know is when exactly (the minute) the process is finished.
Try the following formula which assumes the start date and time are in A2, the number of 8 hours duration days is in B2, the number of duration hours are in C2 and holidays are listed in column F (as shown in the graphic below):Let me know if you have any questions.
Please Login or Register to view this content.
Excel 2010
Row\Col start days hours finish Holidays
Sheet: Sheet1
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hmmm, close, but not exactly what i wanted.
Can you tell me how you did the table? I'd like to insert an example, but I'm a little bit confused by the bbcode.
To give us an example of what you want use the "Go Advanced" button beneath the "Quick Reply" window and upload a copy of your file. Be sure to manually insert your expected result.
I hope this illustrates it a little bit more.
Btw. Thanks for you efforts.
I have researched and trail and errored, and have not been able to come up with a formula. I applied data validation to N13 and made a table that gives the date and time that the task will be completed based on the starting date/time and duration and given the schedule. It is not to hard to extend the schedule (as shown) so perhaps this will be of some use:
End date and time based on duration of task.xlsx
Let me know if you have any questions.
I came up with at least a partial solution that works the way that you want. I transposed the schedule then put the following formula in K10:AVR10The following formula in E10 will then find the column which the duration is reached and return a date/time that matches those in your original file:Please Login or Register to view this content.Here is another copy of the file with the above mentioned changes applied to sheet 2:Please Login or Register to view this content.
End date and time based on duration of task.xlsx
Let me know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks