Time calculation formula that adjusts for duration, start time, lunch, breaks
I need help refining an Excel formula using 12-hour clock entries that calculates A) the end time of a task based on variable start times and B) accounts for lunch and break allowances, and C) adjusts for one or more days if duration + break(s) + lunch(s) cause end time to extend into the next day(s) should decision be to convert to 24-hour clock entries in future. Formulas should be copy-able to other rows for multiple task entries. Existing formula referenced "Parameter table but does not include 12-Hour vs. 24-Hour function.
Looking to refine existing formulas [see attached "Time Calculator.xlsx"] at E12:E16 under "Planned End", where formulas utilize current but changeable Shift start, Shift end, break duration, break start, lunch duration, and lunch start times. As shown, the formula are identical. however they do not all resolve accurately, depending on duration and/or "Planned Start" time because they pick up breaks and/or lunches improperly.
Time-Calculator.xlsx
Looking to refine existing formulas [see attached "Time Calculator.xlsx"] at E12:E16 under "Planned End", where formulas utilize current but changeable Shift start, Shift end, break duration, break start, lunch duration, and lunch start times. As shown, the formula are identical. however they do not all resolve accurately, depending on duration and/or "Planned Start" time because they pick up breaks and/or lunches improperly.
Time-Calculator.xlsx
After my efforts of last night lead to a dead end I took up the chore on the different footing this morning. In the end I had to give up the idea of working with shifts. You see, a formula can only display data which are available to it. Therefore this is all about table design, and the problem is in that you wish to determine how many breaks there will be in a time frame whose end is determined by the quantity of breaks. That attached workbook has the solution on Sheet3, and I think this formula will actually do the job:-
Key to this function is the table "Shifts" which must be extended for as long as the longest job of which you wish to calculate the final completion time. You can calculate dates with very little modification if you provide a Date/Time stamp for Start Time (in place of the current Time only). Note that I have stuck strictly with the Time format, even for the duration for which you had proposed to use metric 10.5 in place of time format 10:30. Here too, the formula could be modified to take the format you prefer.
EXX-130626-Time-Calculator.xlsx
=(LOOKUP($A$19 -LOOKUP($A$19,INDEX(Shifts,0,3),INDEX(Shifts,0,4)) + $B$19,INDEX(Shifts,0,5),INDEX(Shifts,0,3)))+($A$19 -LOOKUP($A$19,INDEX(Shifts,0,3),INDEX(Shifts,0,4)) + $B$19)-LOOKUP($A$19 -LOOKUP($A$19,INDEX(Shifts,0,3),INDEX(Shifts,0,4)) + $B$19,INDEX(Shifts,0,5),INDEX(Shifts,0,3))Key to this function is the table "Shifts" which must be extended for as long as the longest job of which you wish to calculate the final completion time. You can calculate dates with very little modification if you provide a Date/Time stamp for Start Time (in place of the current Time only). Note that I have stuck strictly with the Time format, even for the duration for which you had proposed to use metric 10.5 in place of time format 10:30. Here too, the formula could be modified to take the format you prefer.
EXX-130626-Time-Calculator.xlsx
ASKER
The 'Shifts' table is a nice build to your earlier effort. Kudos.
However, it does not yet return the correct "End Time" which is 4:15 PM (see sheet 1). Instead, it returns 3:15 PM using start time of 5 AM and 10.5 hours duration. Some how, your formula loses an hour in that calculation but that variation differs if you run through my 5 examples at A12 on Sheet 1)
I have not studied the construct of the table, but perhaps its granularity is at issue??
I am returning you file with my notes on Sheet 3, rows 20 & 21
Berry
EXX-130626-Time-Calculator.xlsx
However, it does not yet return the correct "End Time" which is 4:15 PM (see sheet 1). Instead, it returns 3:15 PM using start time of 5 AM and 10.5 hours duration. Some how, your formula loses an hour in that calculation but that variation differs if you run through my 5 examples at A12 on Sheet 1)
I have not studied the construct of the table, but perhaps its granularity is at issue??
I am returning you file with my notes on Sheet 3, rows 20 & 21
Berry
EXX-130626-Time-Calculator.xlsx
The error is in the logic, not the formula or the table. The computation of the remaining time without breaks in it isn't correct. Your head is as good as mine, but I'll figure it out maybe after dinner.
ASKER
You are a trooper! Thanks for what you are trying to do for me.
For now let's not be concerned with task duration *including* breaks and lunches exceeding 24 hours to simplify this exercise as you previously cautioned regarding formula complexity.
Berry
For now let's not be concerned with task duration *including* breaks and lunches exceeding 24 hours to simplify this exercise as you previously cautioned regarding formula complexity.
Berry
My fingers make it 16:30 and they now have the backing of a revised formula which you will find in the attached workbook on the page which is poignantly named "Final".
I streamlined the logic and found that the complication which caused the last failure isn't even required. The table can be consulted for direct results. With the logic now perfectly explained you should be able to build trust in it quickly.
EXX-130627-Time-Calculator.xlsx
I streamlined the logic and found that the complication which caused the last failure isn't even required. The table can be consulted for direct results. With the logic now perfectly explained you should be able to build trust in it quickly.
EXX-130627-Time-Calculator.xlsx
Just saw your post done yesterday before dinner.
The way Excel accounts for time is naturally multi-day. A formula either works or else it doesn't. My formula reads a table. If too big a value is requested it will return the highest it has, even if that is wrong.
Regarding the for now part of your suggestion, for now the formula returns the correct time but no date. A little modification will be required to add that feature.
BTW, you might hide the table on a hidden worksheet and fill the cells in your worksheet with references to the table. That way you wouldn't need to change your present worksheet design.
The way Excel accounts for time is naturally multi-day. A formula either works or else it doesn't. My formula reads a table. If too big a value is requested it will return the highest it has, even if that is wrong.
Regarding the for now part of your suggestion, for now the formula returns the correct time but no date. A little modification will be required to add that feature.
BTW, you might hide the table on a hidden worksheet and fill the cells in your worksheet with references to the table. That way you wouldn't need to change your present worksheet design.
ASKER
Sorry to be so slow responding to your last post. A trip to the hospital came between. I have not had time to study "Final" as yet.. Please bear with me.
Berry
Berry
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for the long response delay. I have yet to evaluate your fine effort and in fairness to you I will close this thread. When I get back to my PC I will try it out and perhaps ask for more help on a different thread request.
Thank you for your help and diligence getting to the "Final" iteration.
Berry
Thank you for your help and diligence getting to the "Final" iteration.
Berry
Berry,
I hope you are doing OK. Thank you for thinking of what might be fair to me at this time. In fact, I think that the solution is, finally, worthy of that name but please do feel free to ask me for any fine tuning that it might still require at any time at all.
All the best!
Faustulus
I hope you are doing OK. Thank you for thinking of what might be fair to me at this time. In fact, I think that the solution is, finally, worthy of that name but please do feel free to ask me for any fine tuning that it might still require at any time at all.
All the best!
Faustulus
ASKER
You are the best.
Thank you for the offer. When able, I'll update you on this project's outcome,
Berry
Thank you for the offer. When able, I'll update you on this project's outcome,
Berry
This is a bit tricky. I am offering a start:-
Open in new window
You will find this formula in the attached workbook Sheet2!K26. The sheet explains it. Refer to the rows 14 to 23 based upon which the formula is developed in row 26 with the final result in K26.Your concept of dividing the day into 2 x 12 hours is very un-Excel because Excel has long decided that a day has 24 hours. To display "military" time or AM/PM is a matter of cell formatting, not of calculation. One day has the integer 1. One minute = 1/1440. 0.5 days is both, 12 hours and 12 noon because in Excel all time starts and ends at midnight.
I suggest that the breaks during a period of work time equal all the breaks during a 24 hour period, minus breaks which ended before the start time, minus break times which commenced after the end time. As my spreadsheet shows my above formula isn't ready to calculate durations of more than 24 hours. It should also produce a wrong result if the calculated end time lands within a break. To eliminate the latter the formula would grow 2 or 3 times in size. Therefore, I would recommend using VBA if this is an issue. Accommodating work periods of more than 24 hours would also increase the formula's size.
Please take a look at the tables I have created (rows 1:10) and how they are used. When and if you implement the formula you may use cell references were I use named ranges and named ranges to specify the tables. That would make more sense. Anyway, we aren't so far from the drawing board yet. Let me know how I can be of further help.
EXX-130624-Time-Calculator.xlsx