Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 966
  • Last Modified:

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
0
Berry Metzger
Asked:
Berry Metzger
  • 7
  • 5
1 Solution
 
FaustulusCommented:
Berrymetz,
This is a bit tricky. I am offering a start:-
=StartTime+(Duration/24)+(INT(-Duration/24)/-12)-LOOKUP(StartTime,$F$4:$F$10,$E$4:$E$10)-(LOOKUP(StartTime+(Duration/24)+(INT(-Duration/24)/-12)-LOOKUP(StartTime,$F$4:$F$10,$E$4:$E$10),$B$4:$B$10,$I$4:$I$10))

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
0
 
FaustulusCommented:
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:-
=(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))

Open in new window

It is designed for row 19 where the start time and duration are in columns A and B.

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
0
 
Berry MetzgerAuthor Commented:
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
FaustulusCommented:
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.
0
 
Berry MetzgerAuthor Commented:
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
0
 
FaustulusCommented:
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
0
 
FaustulusCommented:
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.
0
 
Berry MetzgerAuthor Commented:
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
0
 
FaustulusCommented:
Hope you found the hospital and everybody in it in good health. Take your time.
Faustulus
0
 
Berry MetzgerAuthor Commented:
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
0
 
FaustulusCommented:
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
0
 
Berry MetzgerAuthor Commented:
You are the best.
Thank you for the offer. When able, I'll update you on this project's outcome,
Berry
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now