I assume you know how Excel stores date and time then, so let's get to it with how I would attack this issue. Also, if you don't work 7 days a week, you also have to look at what days do you work, so this is also a case where it would be advisable to use the Analysis Toolpak add-ins that's included with Excel. For these addins to be activated, click on:
Tools>Add ins...
Make sure you have both of the first 2 add-ins showing, the first one being the plain Analysis Toolpak and the second one being the Analysis Toolpak - VBA.
For the time portion, you would need the following:
StartTime (ST)
DurationTime (DT)
Since your start time is 6AM, we will subtract 0.25 from the start time.
We will then add the Duration Time to it, and then divide the sum of that by 15 hours since that is the number of working hours per work day.
If ST also includes the date, we will also need to weed that out before we can do anything else with it.
DT is assumed to be in hours format, so 10.5 would mean 10 hours and 30 minutes.
=((ST - INT(ST) - 0.25 + (DT / 24)) / TimeValue("15:00:00")) - INT((ST - INT(ST) - 0.25 + (DT / 24)) / TimeValue("15:00:00")) * TimeValue("15:00:00") + 0.25
Just so you know, I could have used 0.625 in place of TimeValue("15:00:00"), but wanted to so that particular function is available to use.
For what day it will finish on, you can use the WorkDay function that's in the Analysis Toolpak.
--
Sincerely,
Ronald R. Dodge, Jr.
Master MOUS 2000
Hope someone can help withthis. I have a spread sheet to plan production in our factory. I need a formula to calculate the finish time of a job when i enter the duration however we do not work 24 hours so need to ignore the time between 21:00 and 06:00.
For example if a 10 hour job starts at 18:00 one day it would not finish until 13:00 the following day i.e. 3 hours day one, 7 hours the next day.
Hope this makes sense and someone can help
Thanks in anticipation
Martin
