The past few weeks I have had endless questions about time calculations in Excel. So this week I thought I would share a few with you.
First let’s quickly understand how Excel stores dates and time.
Date and Time 101
Excel stores dates and time as serial numbers. When you format the serial number as a date or time, or date and time as in the example below, it displays it in a date/time format but underlying is still a serial number.
The serial number is made up of two parts.
Serial Number
The digits before the decimal are the date and the digits after the decimal are the time.
Dates in Excel
Dates in Excel start on 1st January 1900. Therefore the serial number for 1/1/1900 is 1.
The serial number for 1/1/2012 is 40909 because it is the forty thousand, nine hundred and ninth day since 31/12/1899*.
*Actually 1/1/2012 is only the 40908 day but Excel includes the date 29th Feb 1900 even though 1900 was not a leap year. This inclusion was intentional to provide compatibility with Lotus 1-2-3 which contained the bug and had the market share when Excel was released!
Time in Excel
Time serial numbers represent a fraction of a 24 hour day.
Convert Time to Decimals
Often we need to convert time to a decimal so that we can calculate hours x rate for the purpose of payroll or billing.
It’s easy. Remember, the serial number is a fraction of a day so you simply multiply it by 24.
Note: you don’t need to enter a date and time. If you enter time only, the date part of the serial number is 0.
OK, now you know the rules let’s look at some examples or working with time.
Shift Work Timesheets and Overtime
Calculating the difference between two times on the same date is as simple as subtracting the start time from the finish time, but it’s not so easy if your start and finish times are on different dates, as in the case of shift workers.
Notice the finish time below for Monday is actually 7AM on Tuesday.
We can use a clever trick to test for time that finishes on a different date by checking whether the finish time is less than the start time, as is the case for Monday and Tuesday above.
Taking the formula in cell G4:
The first part of the formula takes the finish time less the start time and then checks whether the finish time is less than the start time (E4<B4). In the case of Monday (E4<B4) evaluates to TRUE, and since TRUE = 1 it adds 1 to E4-B4 to correctly calculate the time.
Note: if your times are entered with the date and time you can simply subtract one from the other, it’s only in the case where times are entered on their own that you need to test whether the finish time is < the start time.
Rounding Time
I’ve had a few people ask me how to round time in 10 minute increments for the purpose of billing clients at an hourly rate.
The table below shows rounding using the ROUNDUP, MROUND and ROUND functions.
If you want to bill in 30 minute increments change the 10 in the above formulas to 30.
Display Time with Text
I had a question this week asking how to display time in a format that reads ‘2 hours 15 minutes’.
There are quite a few formulas that will concatenate text for the words ‘hours’ and ‘minutes’, but I prefer to simply use a custom number format.
You can see in cell K2 the formula subtracts the start time from the arrival time to give the number of hours late.
I then formatted the cell to show the time with words using a custom number format like this:
The benefit of this approach is that the underlying time value remains in the cell so you can use it in other formulas. For example you might like to add up the Time Late column to get a total time late etc.
Download the Excel workbook. Note: please ensure the file extension is .xlsx when you download the file. It is not a zip file.
For more time and date related tutorials:
Ola says
Time monitoring exercise
Step 1
For minimum of 7 consecutive days monitor the time you spend doing the following activities:
Sleep
In-class time
Studying
Personal maintenance (showers, laundry, housecleaning, grocery shopping, etc.)
Fitness
Paid work
Recreation (other than fitness related)
Notes:
1 If you are doing an activity which could be classified under several categories, record your time under the category representing the main reason for doing the activity.
2 Record time in transit (e.g. riding the bus to class) under the category for which you are in transit (in-class time, in this case).
The full 24 hours of each day must be accounted for.
Step 2
Record your data in an excel spreadsheet in the following format.
This should take the following format:
Task Day 1 Day 2 Day3 Day 4 Day 5 Day 6 Day 7
Draw a pie chart which shows the total amount of time spent doing each activity (it should look similar to the example shown below. Note: your percentages and therefore the corresponding size of each pie wedge will be different)
Step 3 Pie chart specifics:
1. produce only ONE pie chart in excel
2. present the activities in this order (starting from the ‘top’ and going clock-wise and colour the pie pieces in the specified colour:
• Sleep – black
• Classes – red
• Studying – orange
• Personal maintenance – blue
• Fitness – yellow
• Work – green
• Recreation – purple
3. on each pie piece indicate the title of the activity (e.g. sleep) and the percentage of the total time that was spent doing that activity.
Philip Treacy says
Hi Ola,
Looks like you have a well detailed description of what you need to do. Have you tried doing this yourself? We’re happy to help, but we don’t do your home work for you. You won’t learn unless you do it.
Phil
Nick says
Hi
I have a range of start and finish times for all of the employees working in this company. I need to extract certain information out of the resulting hours worked. If an employee works on a Sunday then I need to know how many Sundays are worked in the month and also how many Sunday hours are worked.in a separate column. Another problem I face is that if an employee works on whatever day (including a Sunday) and their shift ends after 6 pm then I need to know how many “shifts” this happened on. I was thinking of using a function to check whether the day worked is a Sunday and then count these “shifts” but it seems excel counts the amount of hours works and not how many “shifts” were worked. With the night shift allowance, the employees get a certain payment per night “shift” worked – what defines a night shift is, is any shift (regardless of the day worked) that end after 6 pm. I was thinking of a function/formula to check if the end time is after 6 pm, apply it to all the end times and count the true results. I think I’ve just worked out this one with hidden columns and statistical functions returning True or False statements..
Mynda Treacy says
Hi Nick,
Please post your question on our Excel forum and include a sample file so we can help you with each issue.
Thanks,
Mynda
Jeff says
How would I calculate the following date difference as an on time delivery if the shipment arrives on the same date?
=IF(K64<=0,"NA",IF(K64<J64,"On Time","Late"))
J64 K64 L64 M64
Contract Destination ETA Delivered Date Latest Status Performance
SEP 08, 2016 05:00:00 PM SEP 08, 2016 07:39:39 AM Delivered Late
Catalin Bombea says
Hi Jeff,
Looks like a simple cell deduction to me (=K64-J64), the cell with this formula should be formatted as “d, hh:mm”
If it’s not what you’re looking for, you can upload a sample file on our forum.
Catalin
Andy Wilkinson says
Hi,
I am trying to plot finish times for jobs that complete between 22:00:00 and 05:00:00 every evening in Excel 2010. The issue I am having is in XY Scatter trying to ensure the times sit around the 00:00:00 centre and plot above and below the line. I am unable to make this happen and instead the scale goes from 00:00:00 to a second 00:00:00 and on to 04:48:00. Any assistance would be great.
Thanks
Catalin Bombea says
Hi Andy,
Please upload to our new forum a sample file with details, when working with time, there can be many issues, only a sample file will clarify the problem.
Thanks for understanding,
Catalin
Rob says
Hi Catalin, I’m tying myself in knots again and need your help please.
I want to remove the automatic 45 min break calculation from the formula but not sure which bits to change.
My total hours cell formula : =IF(F3880,8/24,0)
My overtime time cell formula : =IF(G388>TIME(8,45,0),G388-TIME(8,45,0),0)
My money cell formula : =IF(G388>0,9/24,0)*13*24+IF(G388>TIME(9,45,0),G388-TIME(9,45,0),0)*19.75*24
I’m now charging for all hours without any break deduction so what do I need to get my overtime cell show the total time over 8 hours rather than with 45 mins deducted and likewise for the money calc cell please?
Thank you!
L.E.: Sorry, made a typo there. Money cell formula currently reads =IF(G388>0,8/24,0)*13*24+IF(G388>TIME(8,45,0),G388-TIME(8,45,0),0)*19.75*24
Also I want to change it so 13/hr is now to 10 hours (with a min of 8 hrs) and then 19.75 thereafter please.
Catalin Bombea says
Hi Rob,
The formula should look like this:
Note that I removed those 45 minutes from the TIME function, you should do the same with overtime formula. It might not be exactly what you have in mind, you should clarify the details or apply your own logic in the same manner as the example above. Of course, you can prepare a sample file and upload it to our Help Desk, it will be a lot easier to discuss over a file.
Cheers,
Catalin
Rob says
Hi, that formula doesn’t work and I’ve tried it in all 3 cells :
=IF(AND(G388>0,G388TIME(10,0,0),G388-TIME(10,0,0),0)*19.75*24
Could you please post the new formulas for each of the 3 formulas I posted. Thanks.
Catalin Bombea says
Hi Rob,
As I mentioned in the previous message, you should clarify the details, and upload a sample file on Help Desk, don’t let me do all the work 🙂
“That formula doesn’t work” is not saying anything about what is expected, the initial details are not very clear. The only one who knows all the details is you, if you don’t share what you have in mind, I cannot guess, I hope you understand this point of view.
Let’s continue this on Help Desk please.
Cheers,
Catalin
Rob says
Hi Catalin, very useful site for an excel noob like me!
Your example above on how to calculate shift work is nice but I wonder if there’s a way of factoring in a guaranteed minimum paid hours rather than just working it out from the ACTUAL hours worked?
Eg. as a night shift worker myself I charge £10/hr basic rate for first 8 hours then £15/hr thereafter, however I have a ‘minimum 8 hrs’ clause in my terms so that if I work only 1 hour then they still get billed for 8 regardless.
I am also required to deduct a 45 min break per shift, but the ‘minimum 8 hr’ clause overrides that so an 8 hr shift would still be billed at 8 hrs not 7hrs 15. 8hrs 45 would be billed at 8hrs because the break deduction has kicked in; 10 hrs worked would be 9hrs 25 billed etc.
Is that possible to convert to a formula or would it end up being about 3 lines deep?!
I currently input my start/finish times manually and also the total hours I’ve done less the 45 min break, then multiply the hours by my rates to get a total, eg.
Col E.. F…G..H
1800 0645 8 4
Col I =SUM(G*10)+(H*15)
Wondered if it’s possible to create a formula that takes into account min 8 hrs clause and the 45 min break to correctly calculate these examples :
Start 1800 End 0645 (amount should come out at £140)
Start 1800 End 0030 (amount should come out at £80)
Start 1800 End 0230 (amount should come out at £80)
I look forward to your comments!
Krgds.
Rob
Catalin Bombea says
Hi Rob,
Glad to hear that you found it useful.
For this site, all the credits goes to Mynda and Phil, my contribution is too small compared to their amount of work 🙂
Try this file from our OneDrive folder: Time calculations for Rob
You can download it and test the formulas.
Cheers,
Catalin
Rob says
Hi, thanks a lot! That works great! Could you explain the actual formula though please so I understand what is happening? For example, I don’t understand what the word “TIME” correlates to in the formula you’ve used; I can’t see any reference to it. I don’t know what the significance of column J is either.
=IF(G2>0,8/24,0)*10*24+IF(G2>TIME(8,45,0),G2-TIME(8,45,0),0)*15*24
The sheet shows column E as start time, F as finish time, G as total time, H as standard rate, I as overtime rate.
What do the commas in the formula signify? They confuse me as I don’t know what (8,45,0) means. The rest is self-explanatory for me and I understand the dividing and multiplying by 24 to convert time to decimal and vv for the calculations to work.
Catalin Bombea says
Hi Rob,
You have to read this to understand what the word TIME means: excel-time-function
It’s in fact a function that will return the value of a specific time in sexagesimal system.
The value contained in your cells are in decimal system, even if it’s displayed as 8:00 the value is 0.333333 in that cell. 8:45 is in fact 0.364583333333333, because 8 hours and 45 minutes can be converted to decimal system as =8.75/24. In a formula all the terms must be in the same system, decimal or sexagesimal (excel’s time system). If cell G2 is 12:45, the cell value is in fact 0.53125 , you will see this value if you change the cell’s format to numeric or General. TIME function will convert the hours and minutes to excel’s time system: TIME(8,45,0) will convert 8 hours and 45 minutes to 0.364583333333333 (in any excel function, the comma is separating the function arguments, the arguments for TIME function are described in the link provided above, but you should guess that first argument refers to hours, second argument refers to minutes, and the third argument refers to seconds)
The formula can be used without the TIME function, if you use 0.364583333333333 instead, which is the result of TIME(8,45,0):
=IF(G2>0,8/24,0)*10*24+IF(G2>0.364583333333333,G2-0.364583333333333,0)*15*24
Column J is the result you wanted:
“Start 1800 End 0645 (amount should come out at £140)
Start 1800 End 0030 (amount should come out at £80)
Start 1800 End 0230 (amount should come out at £80)”
Cheers,
Catalin
Rob says
OK, now you’ve pointed it out it’s obvious what the TIME formula related to! I understand that bit now but I still don’t follow how the formula is calculating it all. I don’t understand what the commas do in the formulas, like the G2>0,8/24,0. I get as far as IF G2 is greater than 0…. then I’m lost. Could you write the actual formula in words step by step so that I can understand exactly what the formula is doing/how it’s working please?
Catalin Bombea says
Hi Rob,
If you concentrate to the problem, I’m sure you will understand it 🙂
As I said, commas are arguments separator, to see the meaning of each function used, you have to study the function arguments: if-statement-explained
=IF(logical_test,value_if_true,value_if_false)
In other words, IF(“the logical test is TRUE”, “do this”, “otherwise do that”)
The formula in column K is the complete formula, you don’t have to digest that first, there are 3 columns you should study first, columns H,I,J, which are doing the same thing as column K formula, but splitted in 3 columns. First column, H, calculates normal time, column I calculates extra time, column J calculates the value corresponding to normal time and extra time, according to the values provided by you for hourly costs. Column K formula has all formulas from columns H, I, J, if you can understand these formulas, which are not very difficult, you will be able to understand the complete formula, just take your time and analyze the partial formulas. As they say, no pain no gain, right? 🙂
Cheers,
Catalin
Rob says
OK thanks – yes I did manage to work it out the complete formula whilst my comment was awaiting approval.
Now I have another question. I have a column for meal allowance where up to 10 hrs total time worked qualifies for £5 meal allowance and over 10 hrs qualifies for £10. Where G2 is total time, I worked it so that =IF(G2TIME(10,0,0),10,5 work properly? If I have done 10 hrs it shows as £5 allowance when it should show £10. I had to change that formula to >time(9,59,0),10,5 for it to work properly. Surely ‘greater than 10′ means 10 or greater? Seems to work OK when I do ’10 or less’ as per above so why not the other way round?
Catalin Bombea says
If you want 10 or greater (“greater than or equal to”), you have to use both comparison operators, (>=), not only “greater than” operator. Use: =IF(G2>=TIME(10,0,0),10,5)
If you use only the greater than “>” operator, this will exclude the 10 value from this calculation.
Cheers,
Catalin
Rob says
OK thanks. My last query is how to add in if G2 (total time worked) is 0.00 (not worked that day) then put £0 in meal allowance column. With the current formula it puts £5 because it’s less than 10 hrs.
Catalin Bombea says
Use multiple nested IF conditions:
=If(G2=0,0,IF(G2>=TIME(10,0,0)…..
Catalin
Rob says
Catalin, thanks a lot! That works great!
Catalin Bombea says
Good work Rob, glad to see you managed to solve the problem 🙂
Catalin
Shondrika says
Date In Out In Out Shift Daily Cumulative
Sun 7/26 8:25PM 1:00AM 2:00AM 10:20AM 12:55 12:55 12:55
Fri 7/31 8:19PM 1:00AM 4:41 4:41 55:25
In the information above, my husband time system for his job calculates times as you see her to the minute. My calculations in excel are coming up as 12.92 for Sun and 4.68 for Fri. I downloaded the spreadsheet from Vertex42 and the cells are already formatted as Number, but I still cannot figure out how to make this display to match the ADP time per minute. It rounds 12:00 AM – 1:15 AM to 1.25 instead of 1.15.
Catalin Bombea says
Hi Shondrika,
You have more details for how to work with time formats here: calculating-time-in-excel
1.25 is in decimal system (excel is using the decimal system by default), 1:15 is a time format, which is the same as the sexagesimal format.
If the cell with 1.25 value is formatted as number or General format, you can convert it to be displayed in time format by dividing the decimal value by 24 (=1.25/24), and format the cell as time format.
Cheers,
Catalin
Warren says
I am using Excel 2003 to do up a shift roster for a call center. Once entering start times, calculations are done to provide regular timeouts throughout the shift period.
Using Excel I calculations the duration of ‘in contact’ time (in minutes) needed then need to add value to a ‘time’ formatted cell to display ‘new’ times for the scheduled breaks during the shift.
The periods (minutes) of “in contact” time is are calculated in ‘number’ values, relevant to variables of ‘time of day’ and ‘number of staff’. The calculations are easy. Adding the resultant ‘number’ value to a ‘time’ format seems impossible
I have scoured the Internet [over 4 hours (22 sites)] to find a solution of how I can add a ‘numbers’ outcome to the ‘time’ format to give a resultant new ‘time’ – without any luck – Do you have a suggestion
Thanks
Catalin Bombea says
Hi Warren,
In Excel’s time system days are integers; hours are considered as day fractions (which is obvious 🙂 ); so, one day is the same as 24 hours. To add an hour to a time format, simply add 1/24 to the result…To add minutes, you can use the same logic, keeping in mind that an hour must be divided by 60 to convert to minutes: =A1+17/24/60 will add 17 minutes to the time from cell A1 (same can be written as: = A1+17/(24*60) ) .The cell with this formula must be formatted as time.
Hope it helps,
Catalin
Chona says
I have this leave record that 1day = 8hrs, so if I want to enter
1/2/2014 -> 1 (as 1 day leave)
15/4/2014 -> 1/2 (as half day or 4hrs leave)
24/6/2014 -> 2 (2 days leave)
7/11/2014 -> 2hours (2 hours on leave)
let say this total should be 3days and 6hours how to write this bcoz I want to deduct the leave balance of 4days 2 hours + 8days (current leave) = 12days 2 hours – 3days 6hrs = 8days 4hrs
Catalin Bombea says
Hi Chona,
which is the relationship between those dates you are entering and the number of days leave? The dates are static, or not?
Please prepare a sample workbook with all the details, and upload it to our Help Desk, we will gladly help you.
Cheers,
Catalin
Chona says
I need to record by month the annual leave each worker take.
If the total annual leave is 5 days 2hrs and the total leave they took on jan is 3days 5hrs balance should be 1day 5hrs but if I let excel calculate the balance is 1day 7hrs.
I cant attached the excel file.
Catalin Bombea says
Hi Chona,
You can attach the file using our Help Desk system.
Catalin
Zion Yang says
Hi, i tried reading up on the Shift Work Timesheets and Overtime, however i am unclear how to go about performing the trick test for time that finishes on a different date by checking whether the finish time is less than the start time.
Can i upload my document somewhere to help me with the calculation, thanks!
Catalin Bombea says
Hi Zion,
You can upload your file to our Help Desk System.
Catalin
Mark says
Hi Mynda,
Thanks for all the great tips!!!
I have a problem I ran into while working on a spreadsheet for a co-worker,
I am trying to calculate payments for medical insurance. Payments are based on employees age and the plan they are subscribed to. I have a table that lists the ages and plans and have the pricing at the intersection. I am trying to use an index and match formula to lookup the correct age and plan, then match it up to the appropriate pricing.
The problem I am having is in the age part, I am calculating the employees age by subtracting the employees birthdate form the plans effective date, and trying to use that number in the match part of the formula, I keep getting a #NA error. I think the problem is that the age is not a true number it is a serial number represented by the dates subtracted. Do you have any suggestions?
Thanks, Mark
Catalin Bombea says
Hi Mark,
Can you please upload a sample of your data, to analyze it? There can be many sources for that error, hard to tell without seeing the data and formulas.
Use our Help Desk to upload a sample, i will gladly help you.
Catalin
kevin says
I really need your help. I am pulling my hair out on this one. I am trying to calculate days for vehicle repair. For example, the vehicle arrives on April 15nd at 11AM. It is scheduled for 1.5 days repair, using the “Workday” function, I get a result of April 16th 12:00AM. If I enter 1.8 days, I still get 1 day. Whatever day it gives me, it is always 12:00 AM, never to the hour. My repair shop is open M-F 8:00AM-5:00PM, with one hour lunch, (8 hour work days). I would like to get an accurate result to the hour. Workday removes weekends and holidays, but does not accurately account for hours. Any suggestions? Thank you in advance, Kevin
Catalin Bombea says
Hi Kevin,
That’s a very tricky time calculation, because the starting time is not always the same, end time may exceed the Open schedule.
Another problem is that , i think 0.5 days scheduled repair time means 4 work hours, not really 0.5 days, so you need to be very careful with the meaning of the data.
You can try this file:Work Hours, please test it in all possible scenarios, i may not covered all possible situations.
Catalin
kevin says
Hi Catalin,
Thank you so very much. Every time I think I am mastering Excel, it turns around and gives me a swift headache! Your formula works great! Of course, I will test it and get back to you. I don’t know how to get around the lunch hour problem. It gets more complicated as in the collision repair industry, it is understood that technicians are not constantly working on one vehicle. Although it is not always met, 4 hours per day is the goal. So, if a vehicle has 8 hours repair, in essence that is 2 days! I have built formulas for that as well. Thank you again from San Diego, California and I will reply to you again once fully tested!
Kevin
Catalin Bombea says
You’re wellcome Kevin
I’ll wait for good news from you 🙂
Cheers,
Catalin
SueB says
Good morning,
I have one cell with time value of 673:04:32. I need to change this to a value of minutes. I can do this as (673*60)+04+(32/60). 403842.2 However I have a column I need to work this on. I need to create a working formula so that it will be less manual.
Can you help?
Catalin Bombea says
Hi Sue,
is that value : 673:04:32 entered as text? If it’s a time value, usually you can use the functions HOUR(A1), MINUTE(A1), SECOND(A1) to get those parameters. But in real life, is not so easy all the time, so you better send us an example file, it will be easier for us to undestand your situation and to provide a working solution. You can use our Help Desk: https://www.myonlinetraininghub.com/help-desk
Regards,
Catalin
Wanda Ponto says
I downloaded the workbook and your Shiftwork Timesheet Calculations has a text box indicating columns B through E are formatted as [time: hh:mm] yet Format > Custom shows them being formatted as: [h:mm AM/PM]. Your format of hh:mm doesn’t add the AM/PM on the end and all the times displayed in columns B through E shows AM/PM indicators…
Mynda Treacy says
Hi Wanda,
I was just trying to point out that columns B to E were formatted as time as opposed to G to J which are decimals. The hh:mm notation it in the text box wasn’t meant to be instructions on how to format the cells, however I can see why you were confused initially and I’m sorry.
Kind regards,
Mynda.
Tim Stacy says
Hi,
I want to calculate the number of days between two dates (US view): 01/01/2013 to 12/31/2013. I would expect to find 365 days in 2013 but Excel is telling me 364. Why is this happening?
Many thanks,
“Stuck in time”
Catalin Bombea says
Hi Tim,
From your dates, excel calculates the days starting from 01/01/2013 00:00 to 12/31/2013 00:00. This means that december 31 is not included…
To include the last day of year 2013, use: 12/31/2013 11:59:59 PM instead of just 12/31/2013, or use 01/01/2014 for the second date, this way the count will include dec 31. (as 12/31/2013 11:59:59 PM is very close to 01/01/2014 00:00 AM)
Hope it’s clear enough, 🙂
Catalin
NDIP SIMON ARREY says
I WOULD LIKE TO APPRECIATE YOUR ENDLESS EFFORT TO EDUCATE AND FACILITATE WORK FOR ALL THROUGH THIS FREE ONLINE TUTORIALS. I HAVE GAINED A LOT AND I AM PROUD TO PERFORM MY DAILY TASK ON MY JOB SITE WITH EASY.
THANK YOU VERY MUCH.
NDIP SIMON ARREY says
please i would wish to know how to calculate the number of months only between two dates.
Mynda Treacy says
Hi Ndip,
You can use the DATEDIF function for this.
Kind regards,
Mynda.
Mynda Treacy says
Thanks, Ndip. I’m glad I could help 🙂
Kourtney says
Greetings,
How do I write my formula to count a specified number of workdays beyond a specified date?
For example, upon completing phase 1 work (say 9/17/13), I must wait 10 work days before starting phase 2 on day 11 (10/2/13).
In my workbook, the “phase 1” completion date is in its own cell; for sake of example, A1 = 9/17/13. My initial attempt of =SUM(A1+11) results in use of calendar days and returns a date of 9/28/13. I cannot find reference of how to specify the use of work days.
Regards,
Kourtney
Mynda Treacy says
Hi Kourtney,
You can use the WORKDAY function for this:
Kind regards,
Mynda.
Kourtney says
Thank you so much, Mynda, I really appreciate it.
P.S. Thank you for managing this forum. I have found your tutorials extremely helpful!
Mynda Treacy says
You’re welcome, Kourtney. Glad I could help 🙂
Michael Tracy says
I am attempting to do something similar to your example above under Shift Work Timesheets and Overtime, however I am tracking time without breaks, but instead adding together 5 different possible tasks during a day, and am timing each one with the start time and finish time. For example, I want the total time from Task 1 + Task 2 + Task 3, + Task 5 at the end of the day. Due to the shift times, the time crosses midnight during the shift, but leaving a possibility that not all tasks will be done that day.
Task 1 may be from 6 PM-8PM, and Task 2 from 9-11PM and Task 3 from 11:30PM-1:00 AM, without getting to tasks 4 and 5. I need to total up the times from the first 3 tasks with accounting for it crossing midnight.
My first formula would only work for the times on the same evening, but becomes invalid after midnight, and shows all ####. It was a simple Finish time – Start time for each, and was added up at the end.
I then attempted to use the example above and created a new forumla, which worked on the first couple items, but then showed incorrect times for others after dragging the formula down. It was over estimating the times, and seemed to be adding 4 extra hours in on some of them, but it worked in others.
=(C3-B3+(C3<B3))+(E3-D3+(E3<D3))+(G3-F3+(G3<F3))+(I3-H3+(I3<H3))+(K3-J3+(K3<J3))*24
I would be happy to send a picture at your request of the spreadsheet to give you a better idea of what I am trying to do. Thank you so much for your help.
Mynda Treacy says
Hi Michael,
If you can send me an extract of the worksheet via the Help Desk I’ll take a look.
A screen shot helps but I still have to recreate all of your data just to test some things, so if you can help me out by instead sending me an Excel workbook containing your data (or extract if it’s sensitive), I’ll gladly look at a solution for you.
Kind regards,
Mynda.
Francis says
Could you explain to me how to use worksheet controls like Combo or List Box refers and dealing with interactive Chart Data but using only INDEX()worksheet function?? Can this be done to draw from to create a
Interactive Chart to make it alive by moving List Box or some kind of Combo status?? I hope others may be interesting in learn a better way to make this possible. Thanks
For example
2005 2006 2007 2008 2009 2010
alpha 16 18 17 12 18 11
beta 14 17 13 17 17 18
gamma 13 12 13 10 11 9
delta 10 9 13 14 8 12
epsilon 8 8 9 13 12 13
Totals 61 64 65 66 66 63
2005 2006 2007 2008 2009 2010
Alpha Use the index formula here
beta Also use the index formula here too
Mynda Treacy says
Hi Francis,
Yes, you can use only the INDEX function to retreive data dynamically based on selections made from a Combo Box or List Box. I cover these techniques in my Excel Dashboard course.
Thanks for asking, however they are too detailed to cover in response to a comment.
Kind regards,
Mynda.
M.Ali Ahmadi says
Hi, and have a good time,
I am very happy since I find your site and leaned a lot about Excel, thank you very much.
Be successful in your life.
M.Ali
Mynda Treacy says
You’re welcome, M. Ali. Glad we could help 🙂
khalid says
owsume…great tips
Mynda Treacy says
You’re welcome, Khalid 🙂
Abdi says
If in a week Workday Monday to Friday (5 days work)
Times start to work everyday :
Mon to Thurs : 07:00 t0 16:00 break 12:00 to 13:00
Friday : 07:00 t0 17:00 break 11:30 to 13:30
Q : How to calculate overtime for 3 days ( W, T, F ) if finish work at 22:00 ?
Regards
Mynda Treacy says
Hi Abdi,
There is an example of how to lay out a time sheet calculation for overtime under the heading ‘Shift Work Timesheets and Overtime’ in the above post.
You need to establish what the normal hours are and deduct them from the total hours to get the overtime.
Kind regards,
Mynda.
Neil Cormack says
Hi,
I wonder if you can help me.
I am trying to achieve 2 things;
1 is a calculation to show if a rota has allocated too many hours for a member of staff and if it exceeds a total set number of hours a simple “Too High” message is displayed.
2 is to enable me to print out a simple rota from the above calculation jusst showing start and finish times and total hours to be worked.
Appreciate your asistance.
Regards
Neil
Carlo Estopia says
Hi Neil,
Pardon me but your description is too general for me.
I suggest you send a mock file and some description of
your business logic/computation here so we can convert it
to a formula :HELP DESK.
Right now, I can only suggest you read
IF functions
Time calculations
Cheers,
CarloE
Bob says
Hi there,
I wonder if you can assist me please, at the age of 73 I am doing a little project regarding horse racing and after a copy and paste of this time
1-17.84 (representing 1 minute 17.84 seconds could you assist me in coverting this to seconds in Excell. I have tried everything and read a lot but still confused.
Thanks in advance
Bob
Mynda Treacy says
Hi Bob,
If your data is always formatted as 1 digit – 2 digits . 2 digits you can use this formula:
=LEFT(A1,1)*60+RIGHT(A1,5)
Where your time is in cell A1.
Kind regards,
Mynda.
Andrew says
Hi,
I am trying to calculate hours that before 8am is overtime and after 8am is normal time.
If I have a start time of 7:30am and an end time of 9:00am I calculate .5 hrs @$100 and 1.0 hrs @ $75.
All works as expected, except if I have a start time of 6:00am and a finish time of 7:30am which is less than the 8am rate change time.
I get a result of 2.0hrs. (8am-6am)
How do I account for start and finish times that dont span the rate change time of 8:00am
Carlo Estopia says
Hi Andrew,
I expect to anticipate more scenarios here so I suggest you send us your file through HELP DESK .
Anyway I tried to improvise Mynda’s shift calculation tricks here. .
Here it is:
GIVEN:
(B4)START –
(E4)OUT –
FORMULAS: Copy it on the Cells () provided(G4 H4 I4 J4)
(G4) TOTAL HOURS =(E4-B4+(E4
less: OT
(H4)=IF(AND(B4
= Regular Hours. (Total Hours less OT HOURS)
(J4)=G4-H4-I4
EXPLANATION OF THE OT FORMULA:
H4 – IF START is between 12 AM and 8 AM
IF END(E4) > 8 AM then
OT = 8 AM – START(B4)
ELSEIF END(e4) <8 AM
OT = END(E4) - START (b4)
ELSE
ZERO --- If START does no fall within 12 AM and 8 AM
I4 - IF START is between 8 AM and 11:59 PM
IF END(E4) < START(B4) then OT = 11:59 - START(B4) + 1 min (or 12 midnight - START(B4) but we can't do this in EXCEL) PLUS END(E4) - 12 AM ELSE ZERO ---- IF START does not fall within 8 AM and 12 MIDNIGHT(11:59 PM with EXCEL) In a nutshell this example would like this Start End Total Hours OT1 OT2 Regular hours Note that OT1 and OT2 are exclusive of each other. OT1 is bet 12 am and 8 am , OT2 is between 8 am and 11:59 PM/12 AM Sincerely, CarloE PS:Limitations: No Breaks considered in this example. I'll wait for your file first before we will complicate this some more. Cheers!
Bob says
I have a rather tricky calculation I need to perform in Excel. I need to calculate times for response and repair in data I export from a trouble ticket system. The calculations are based on several SLA’s. One is 24/7/365 (Priority),2 Hour Response, 4 Hour Repair. Another is 8 – 5 M-F excluding holidays (Non-Priority), 4 Business Hour Response, 8 Business Hour Repair. Another is 8 – 5 M – F excluding Holidays (Field), 4 Business Hour Response, 16 Business Hour Repair. I have Assigned to Response times which I need to calculate based on the SLA level which requires I define the time the day starts and ends in order to calculate based on the criteria for the SLA. Unfortunately the Priority SLA is based on 24 hours which I must calculate regardless of the time of day while the other calculations are based on a business day. Same goes for Repair calculations which run from Response time to Fixed time. I do have a field with the SLA to be measured by (R), Assigned Time (AA), In Progress Time (AB), Fixed Time (AC) and a Pending Time (AF). If there is an entry in Pending Time it is used too calculate Fixed Time (Used when client creates a situation thet takes repair time out of our control)
Mynda Treacy says
Hi Bob,
The Shift Work Timesheets example above deals with time that spans two days. Alternatively the simplest way to deal with time that crosses into the next day is to enter the time and date in the cell. Then it’s as simple as the end date & time minus the start date & time.
e.g. 1/1/13 11:00 PM
You can format the cell to only display the time if you want.
I hope that helps.
Kind regards,
Mynda.
Mynda Treacy says
One of our members, Eduard, kindly shared a workbook with us that could help you understand time in Excel.
You can download it here.
Thanks, Eduard 🙂