Solved

Time calculation formula that adjusts for duration, start time, lunch, breaks

Posted on 2013-06-25
12
822 Views
Last Modified: 2013-07-05
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
Comment
Question by:Berry Metzger
  • 7
  • 5
12 Comments
 
LVL 14

Expert Comment

by:Faustulus
ID: 39275207
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
 
LVL 14

Expert Comment

by:Faustulus
ID: 39277780
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
 

Author Comment

by:Berry Metzger
ID: 39280364
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
 
LVL 14

Expert Comment

by:Faustulus
ID: 39280928
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
 

Author Comment

by:Berry Metzger
ID: 39280975
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
 
LVL 14

Expert Comment

by:Faustulus
ID: 39283471
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 14

Expert Comment

by:Faustulus
ID: 39283655
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
 

Author Comment

by:Berry Metzger
ID: 39287601
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
 
LVL 14

Accepted Solution

by:
Faustulus earned 500 total points
ID: 39287694
Hope you found the hospital and everybody in it in good health. Take your time.
Faustulus
0
 

Author Closing Comment

by:Berry Metzger
ID: 39302125
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
 
LVL 14

Expert Comment

by:Faustulus
ID: 39303344
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
 

Author Comment

by:Berry Metzger
ID: 39303399
You are the best.
Thank you for the offer. When able, I'll update you on this project's outcome,
Berry
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now