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.
Enter your email address below to download the sample workbook.
For more time and date related tutorials:
Excel Factor Voting Roundup
Orcadian
I want to plot time data that covers the range 22.00 to 04:00. Despite several days of reading posts, docs & suggestions, I cannot find a way of making the data plot in a continuous time sequence.
If I set x-axis bounds of 0.0 to 1.05, the x axis runs from 00.00 to 00.00 and I get two clusters of points – one at far left and one at the far right. If I set bounds of 0.5 to 1.5, the chart runs from 12.00 to 12.00 with the 22:00 to 23:59 data in the centre – but data from midnight and 04:00 are not shown.
What I want to see is an x-axis going from say 21:00 to 21:00 with the data smoothly displayed from left to right between these bounds. Is there any way to achieve this?
Mynda Treacy
You need to include the date so that the time stamps are in the correct order e.g. 23/1/2023 21:00, 24/1/2023 4:00
Rick
I need the time displayed as military i.e. start is either 00:00 or 24:00 either is okay and then end is say 07:00 then I need it to calculate the difference to be 24-7=7 hrs. What is happening is that it literally interprets this as24-7 to equal 17hrs. Which if I am doing accurate time accounting is a major issue and mistake.
If you could address this question that would be fantastic. Thank you
Mynda Treacy
Hi Rick,
Please see the information in the above post under the heading “Shift Work Timesheets and Overtime” which explains what you need to do for it to calculate the way you want.
Mynda
George
Good day, How can I subtract 1 from 20 everyday at 8:00 AM?
Assuming that time is indicated in cell A2. Thanks
Mynda Treacy
Hi George,
I’m not sure what you mean. Please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Mynda
Devika P G
8 health care home having different shifts and different time period.eg:
ABC -Early shift -08:00-20.00
DEF – early -07.30-9.30
etc. there are 4 types of shifts.early,day,late,night.each carrying different time period in different homes.
I need to find the time period automatically when we enter the home name and type of shifts.please help me
eg: when I enter the ABC home,and Early shift,I need to show in the next cell that automatically showing the time period.
Mynda Treacy
Hi Devika, please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Sajji Oomman
Thank you Mynda
Sajji
Mynda Treacy
My pleasure 🙂
Sajji Oomman
I’m working in manufacturing company. I would like to discuss with u all about a problem which i’m facing here. i’m taking care on the canteen service the problem which i’m facing is:
1) We have contract with an external catering service fixed agreement 140 Pax with the vendor extra will count on above 140 Pax.
2) Total strength of my company is 140, Flouting would be around 20 No’s. In this some people will carry their own box and pickup the plate from canteen that plate also count and the and some will be absentees. I don’t have proper system to monitor the canteen service.
I cannot stand there and count the heads everyday cos it might cause misunderstanding between the canteen emp and management.
Please suggest any template update automatically in excel so that employees can order daily on that template. or other way to sort this issue.
Pls help me out to sort out this issue.
Mynda Treacy
Hi Sajji, We don’t have any templates, but perhaps you could use Microsoft Forms for your employee’s to order their meals. Mynda
mahboubulllah.jammay
Hello, you have provided best, simple and user friendly guides for time calculation, still I have a problem in my time calculation: I want to have a worksheet to calculate my daily times worked, hours for pray and lunch and times spent in personal phone calls etc… deducted.
example we have to work 8 hours a day including 1 hour for lunch and pray, I am recording my time in and out, time going for pray and lunch and coming back, minutes/seconds spent in personal phone calls etc…(time in 8:10, time out 17:11, going for pray and lunch 12:30 – 13:33, personal phone calls 00:03:15 + 00:00:33+00:01:28) and I want to have a work sheet to calculate my hours really worked and show the difference with hours to be worked and give me daily and monthly over/under hours worked than labor law, to calculate overtime or deduct salary for less working than law, if you provide this worksheet it will be very useful for me and any one who want to get exact salary honestly
Mynda Treacy
Thanks for your kind words 🙂 We don’t build templates, sorry. However, if you get stuck building it yourself you’re welcome to post your question and Excel file on our forum where we can help you.
Ravi
Hi
Please can you Let me know How to subtract 18MAY2020:15:51:28.00 from 17FEB2020:11:40:11.00??
Catalin Bombea
Hi Ravi,
To convert to real dates, you have to manipulate the string:
assuming that in A1 you have 18MAY2020:15:51:28.00, use this formula to convert:
=DATEVALUE(LEFT(A1,2)&” “&SUBSTITUTE(LEFT(A1,FIND(“:”,A1)-5),LEFT(A1,2),””)& ” “&RIGHT(LEFT(A1,FIND(“:”,A1)-1),4))+TIMEVALUE(RIGHT(A1,11))
Use the same formula for the second value. After you convert them, just subtract the results.
Terrence Reynolds
good day
is it possible to make a formula where the start time is known eg : 6am and a time period eg 10 hrs to determine what time the cycle will finish ie in this case its : 4pm
Catalin Bombea
If you simply add those 2 cells, you will get the result.
Just make sure you type =10/24 for the time period, not just 10. (10 is in decimal system, 06:00AM is in a time format)
DanT
I have a task that take 18 hours and needs to be finished on Wednesday at noon. I cannot work everyday on it, here is my schedule: Monday no work, Tuesday from 10:00 am to 2:00 pm, Wednesday from 8:00 am to 4:00 pm, Thursday from 10:00 am to 3:00 pm, Friday from 11:00 am to 3:00 pm, Saturday from 10:00 am to 1:00 pm, Sun no work.
how to I calculate the day and the hour the task need to start?
thank you
Mynda Treacy
Hi Dan, this sounds like a homework question 😉 Please post your Excel sample file showing how your data is structured and your question on our forum where we can help you further.
Mynda
Mark Anthony
Hello Sir,
I want to ask regarding formulas on excel.
I want to create a Time In and Time out only, also will compute the late and OT.
I’m planning a 8:30am time in and 5:30pm timebut I don’t know how to formulate in on excel.
I hope you can help me regarding this problem.
Thank you.
Mynda Treacy
Hi Mark,
Please post your question on our Excel forum along with your sample Excel file showing what you want to achieve. We can help you further from there.
Thanks,
Mynda
Alp
Very nice explanation thanks
Mynda Treacy
Glad you found it helpful 🙂
Kelli
Hello, I was wondering if you could help me? I’m trying to make a schedule for the week. But in one cell I would like the time to read as 7am – 2pm and then at the end of the chart have the hours read for the week for each individual person. I’m not even sure if this is possible.
Catalin Bombea
Hi Kelli,
The data structure is not clear, can you upload a sample file on our forum with a manual example of the expected result?
Laura
Hi I’m making an excel Gantt chart using conditional formatting. The gantt is covering 2 days, broken down by hours. So starts at 6am on 1st August, ends at 5am on 2nd August.
When I put in the following details it highlights the wrong times:
– shift start: 3am
– shift end 8am
The conditional formatting highlights 3-5am on 1 August (far right of gantt) then also 6-8am on 1 August (far left of gantt), instead of 6-8am on 2 August.
Any tips on how to get this to work?
Mynda Treacy
Hi Laura,
Please refer to this post for an explanation of how Conditional Formatting formulas evaluate, which might help you troubleshoot. And this post on creating Gantt charts with Conditional Formatting.
If you’re still stuck, please post your question on our Excel forum where you can upload an Excel file with your sample data and desired result. We can then help you with the solution.
Mynda
diane
new formula for start time – finish time =hour rate plus amount
Catalin Bombea
Hi Diane,
What do you mean? Is that a suggestion or a request?
Jeanine
I am trying to figure this math problem
Where told to figure out
Split one can twice a day how many cans would that be a day ???
I come up with two cans per day , am I right ?
Catalin Bombea
2 cans will be if you split once a day. I guess you will have 3, but I doubt that it’s a math problem, sounds like a trick question.
joe
looking for a formula to calculate what time an employee needs to clock out. E.g. employee has worked 33.68 hours and needs to end with 40 hours. If the employee start time is at 8:00am and has 1 hour lunch, need to calculate the end time to be exactly 40 hours. Any help would be great, thanks.
Philip Treacy
Hi Joe,
It’s always better if you supply the workbook as I think I might be missing some important bits of information here.
If the employee must work 40 hours, starts at 8am and has a 1 hour lunch, assuming they work a 5 day week (Mon-Fri), they must clock out at 5pm on Friday.
So I’m not sure where/why you need a formula for that?
Regards
Phil
Tan Sri Hambali
Hi there.
I’ve been searching over and over website regarding about my query.
Can you help me with this.
It’s about “send message”.
How to make “send message” appear when someone late more than 3 times in a month for example.
Hoping you can help me with this.
Thank you so much.
Philip Treacy
Hi,
I’m not really clear on what you are after. Can you please start a topic on the forum and supply sample data in a workbook.
Regards
Phil
sumanto das
hello sir,
i want to create an excel sheet where i want to give points to people who will finish their work in the given time period .
4:00am-8:00am = 30points
8:01am-9:00am = 25points
9:01am-2:00pm = 20points
2:01pm-5:00pm = 15points
5:01pm-6:00pm = 10points
6:01pm-7:00pm = 5points
7:01pm-9:00pm = 0points
9:01pm-3:59am =(-5)points
for example – Mr X has completed the task at 10:20am .I will enter 10:20am and the excel sheet will automatically take 20 points(9:01am-2:00pm = 20points)
this way i will calculate the final points at the end of every week against every people.
i hope you were able to understand my question .
kindly help.
Catalin Bombea
Hi Sumanto,
Use this formula:
=INDEX({-5, 30, 25, 20, 15, 10, 5, 0},MATCH(C1,{0,0.16666, 0.3333, 0,375, 0.58333, 0.708333, 0.75, 0.791666, 0.875},1))
The numbers in the MATCH function represents time in decimal format, for example 9:00 PM is =21/24=0.875
Sultan
Hi All,
i have 4:28 (hr:min) and wanna add each two hour 15 min, what is the calculation in excel sheet ?
Catalin Bombea
Hi Sultan,
=(HOUR(A1)/2*0.25)/24 should give you the amount to be added to A1 value.
Regards,
Catalin
ZK
Hi
I admire your skills but I’v got lost.
If I want to round up the time to a multiple of 10 minutes, I use = MROUND ((B7-A7), 10/1440),
with foramting “[h]: mm”.
What kind of infrmation gives eg 0.83 (cell G7)?
Regards
ZK
Mynda Treacy
Hi ZK,
I’m sorry I don’t understand your question. If you’re having trouble rounding time, please post your question on our Excel forum where you can upload your sample Excel file so we can see what you’re trying to do.
Thanks,
Mynda
Larry Kolk
I would like to make a time sheet that always rounds to the nearest 15 mins. But also favors the employee on times clocked in and the employer on times clocking out. This is tricky because if they are late say a few minutes or early a few minutes this reverses the function. All I have so far is this, which doesn’t do the trick, one because I’m not doing all the rounding, and two because it is not adjusting in the manner described. This is difficult to wrap my head around :
=IFERROR(((FLOOR(((F13-C13+(F13<C13))),"00:15")-(CEILING(((E13-D13+(E13)
Catalin Bombea
Hi Larry,
Looks like you are not using just time in and time out, there are 4 cells used in your formula: C13, D13, E13, F13.
Can you please upload a sample file on our forum, to see an example of your data? You can use our forum to upload, create a new topic after sign-up.
See you there
Catalin
Juliana
This is great. The trouble I’m having is what do do if there’s a day an employee comes in then leaves after 3 hours with no lunch break. My formula doesn’t work if all 4 cells (Time in – Meal Out – Meal in – Time Out) aren’t filled.
Mynda Treacy
Hi Juliana,
You haven’t shared your formula so I can’t help. A simple formula:
=time out – meal in + meal out – time in
should work. Please post your question and a sample Excel file on our forum where we can help you.
Mynda
Tony
HI ,
I WOULD LIKE TO KNOW HOW TO SHOW + MINUTES IF LATE ARRIVAL AND – MINUTES IF EARLY.
EX 10:00 ARRIVED 10:10 = +10
10:00 ARRIVED 09:55 = – 5
IS THERE A FORMULA FOR SUCH WORKOUT?
Thank you
Mynda Treacy
Hi Tony,
That’s a little tricky because you can’t have negative time in Excel. Instead you have to convert the time to decimals or fractions of an hour.
Try this formula, where column A contains the arrival time:
Mynda
S. Imran
Dear Respected Sir /Maam
can you help me with us? I have a time attendance sheet in excel same like this below but I didn’t get the late timing please help me how can I solved this problem.
In time Out time In time Out time Working/ H Regular/ H Over Time Late time
======= ======== ======= ======== ========= ========= ======== ========
9:00 am 13:00 pm 17:00 pm 21:30 pm 8:30 8:00 0:30 ?
In time Out time In time Out time Working/ H Regular/ H Over Time Late time
======= ======== ======= ======== ========= ========= ======== ========
9:00 am 13:00 pm 17:30 pm 21:00 pm 7:30 8:00 0:00 ? <=== How
Thanks:
Catalin Bombea
Hi Imran,
Can you please upload a sample file on our forum? Time calculations are tricky, it’s better to see the data.
Create a new topic after sign-in
Cheers,
Catalin
Shekhar H
Hey Friend
I need to get total transit time in hr:mm of our vehicles.
Ex. Out time 28/may/2018 13:30:00
Report time 30/may/2018 10:00:00
How can I got the result in 44:30:00 format
Plz help
Mynda Treacy
Hi Shekhar,
Subtract Ex. Out Time from Report Time and format the cell with custom number format
Mynda
Daniel H
Hi, I’m trying to figure out how to check late attendace for my excel. I’m using this formula =IF(AND(B3>=J4,B3=J5),1,0)) in which excel does not want to accept. The logic is that B3 is the starting time (in this case 7:00), J4 is 7:16 (the maximum amount of time allowed for morning shift) and J5 is 19:16 (night shift). I needed the formula to output either 1 or 0. Do you have better idea on how to solve this? Thanks
Catalin Bombea
Hi Daniel,
The syntax is not wrong, the only problem is that you have 2 closing paranthesis at the end of the formula, only one is needed.
Cheers,
Catalin
Ola
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Use multiple nested IF conditions:
=If(G2=0,0,IF(G2>=TIME(10,0,0)…..
Catalin
Rob
Catalin, thanks a lot! That works great!
Catalin Bombea
Good work Rob, glad to see you managed to solve the problem 🙂
Catalin
Shondrika
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
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
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
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
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
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
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
Hi Chona,
You can attach the file using our Help Desk system.
Catalin
Zion Yang
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
Hi Zion,
You can upload your file to our Help Desk System.
Catalin
Mark
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
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
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
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
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
You’re wellcome Kevin
I’ll wait for good news from you 🙂
Cheers,
Catalin
SueB
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
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
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
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
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
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
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
please i would wish to know how to calculate the number of months only between two dates.
Mynda Treacy
Hi Ndip,
You can use the DATEDIF function for this.
Kind regards,
Mynda.
Mynda Treacy
Thanks, Ndip. I’m glad I could help 🙂
Kourtney
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
Hi Kourtney,
You can use the WORKDAY function for this:
Kind regards,
Mynda.
Kourtney
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
You’re welcome, Kourtney. Glad I could help 🙂
Michael Tracy
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
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
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
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
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
You’re welcome, M. Ali. Glad we could help 🙂
khalid
owsume…great tips
Mynda Treacy
You’re welcome, Khalid 🙂
Abdi
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
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
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
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
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
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
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
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=TIME(0,0,0)),IF(E4>TIME(8,0,0),(TIME(8,0,0)-B4)*24,(E4-B4)*24),0)
(I4)=IF(AND(B4>TIME(8,0,0),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
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
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
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 🙂