[2 days left] Whatâ€™s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

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

Posted on 2013-06-25
Medium Priority
952 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
Question by:Berry Metzger
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 7
• 5
12 Comments

LVL 14

Expert Comment

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

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

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

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

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

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

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

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

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

Author Closing Comment

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

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

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

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month14 days, 9 hours left to enroll

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

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