Solved

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

Posted on 2013-06-25
12
864 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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
 
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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Generate a certain future date depending on current cell date. 15 30
Excel IF formula 3 21
Delete all empty columns using VBA 7 42
Data Copy 4 28
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

821 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