# Calculating Elapsed Time in Excel

Let’s say that to make a point about the relationship between hot water usage and the size of the utility bill, you want to track how long your teenager spends in the shower. Or, in planning for a lawn care business, you want to track times required to mow lawns of various sizes. Excel would be a good tool for these kinds of tasks.
Excel’s ability to perform complex calculations is well known, but asking it to return simple time calculations can produce confusing results unless you understand all the factors involved.

At the heart of date/time values in Excel is the fact that they are stored as serial numbers that indicate days and decimals of days since the beginning of January, 1900. Excel refers to these as serial numbers, but they are not serial numbers as you may be used to seeing them as a mix of letters and numbers. They are instead numbers stored in a dddd.tttt format, where the digits to the left of the decimal point tell the number of days since January 1900 and the decimal tells the part of the day that has passed since midnight. This format is known as “serial date” or “serial date-time”. If you enter the current date and time in a cell and set the format to General or Number, you will see the date’s serial number version. April 13, 2009 1:39 pm is represented by the number 39916.56875. Understanding how Excel looks at dates and times can help us to demystify time calculations.
In this example I wanted to know the elapsed time between the first and second values:

As you can see I wanted to calculate how much time elapsed between 8:30 AM and 10:30 PM by subtracting the 8:30 start time value in cell A2 from the 10:30 PM end time in cell B2. The start and stop times were entered with AM and PM formats. Behind the scenes, Excel interpreted the start and stop times in terms of a 24 hour clock and saw the problem this way:
22:30 – 8:30 = 14:00
By default, Excel applied the AM/PM format to the result and returned 2:00 PM which is 14:00 on a 24 hour clock. So it calculated the correct answer – 14 hours of elapsed time – but I want to display that answer in hours and minutes.
To fix this problem, give cell C2, the result cell, the preferred format. Open the Format Cells dialog box and choose the Custom category on the Number tab. Then in the choices at the right, choose the h:mm format. The difference or elapsed time is 14 hours.
Note that the same problem with the start/end times entered with 24 hour time shows how Excel worked with the AM/PM time entries.

What does Excel do when the ending hour is actually a smaller number than the starting hour? This type of situation occurs when a worker begins a shift at 11:00 pm and ends at 7:00 am the next morning. What is important to remember in this time calculation is that while our focus might be on the number of hours and minutes that have elapsed, the number describing the day has also advanced by one as well.
In the example below you can see the result of the simple formula to subtract an evening start time from a next morning end time. The formula returns a negative number so Excel displays the #### symbols.

You can use the IF function to accurately calculate spans of time that stretch from one day to the next . The formula below contains the simple subtraction of the start value from the end value; but it also tests, using the IF function, the value of the end time to see if it is smaller than the start time. In cases where the end time is smaller, 1 (the value of another day) is added to the result.

Another surprising result can appear when adding several amounts of time that total more than 24 hours as in the example below:

You can see that the intent is to show total hours worked in cell B6 using the Sum function. It is easy to see by quick estimation that the total of hours worked was more than 9 hours and 45 minutes. Once again this is a cell formatting problem.
Excel seems to assume that you know these amounts add up to a day or days and some amount of hours and minutes more than a day. Given the current format applied to cell B6, Excel is just displaying the “hours and minutes” part of the answer. To specify your preference to display the entire time here, including the days, expressed in hours and minutes, you must use square brackets in the format. The number of hours and minutes in this sum will show in cell B6 if we apply the custom format of [h]:mm.

Remember when you work with time calculations in Excel:
1. consider how Excel is storing the time value in memory – as a serial decimal number and
2. make sure that the cell format displays the time as you intended

1. Jeremy Bailey

Hi Veronica,

That was very helpful for a different project I am working on and will certainly use it. However, for this one in particular the clock is just a representation of a clock – for a card based basketball game. So, instead of actually counting down seconds from 12:00 to 0:00 the user needs to be able to click a button to subtract time from 12:00 in :20 or :10 second increments by the click of a button ( used command buttons for this) until they get to 0:00 that’s why I used the nesting function. So, I am guessing that is some sort of formula where I start with 12:00 and create a formula using a Macro and VBA (just started learning this) to subtract :20 and :10 by choice with a button. So, it really is a number of game “turns” being counted but represented by a “clock” because it is a sports game. Thank you so much you are very generous with your time – this site has been awesome for learning and seeing what others do.

Thanks,

Jeremy

2. Lynn

I am having trouble with coming up with an excel formula that will total the number of hours worked per shift and automatically subtract 45 minutes for a meal break. I know I could do this with b1-a1+d1-c1 but due to the page size available to me it would be easier if I could do finish time less start time minus 45minutes or 60 minutes for example. So something that would resemble b1-a1- 45 minutes to show my results I am using cell formatting of Customer with [h]:mm

• Veronica Mount

Lynn,
I placed the standard meal break time in a cell off to the side of my data. I called that cell “lunch” but you wouldn’t have to name it. I then subtracted start time from end time, as in B1-A1 and then subtracted the value in the cell called lunch:
=(B1-A1)-lunch
I made this formula a little more simplistic than you would need for employees who work a night shift. If you have that situation you would have to expand the basic calculation to:
=if(B1>A1, B1-A1, (B1+1)-A1)-lunch
Veronica

3. John

Hi Veronica,

I’m trying to compute for hourly wage. For this example, the ff. are the given:
HOURLY RATE (in \$) = 2.00 (in cell A1)
TIME IN- 8:00 (in cell C6)
TIME OUT- 12:00 (in cell D6)
HOURS WORKED- 4:00 (in cell E6)

When I subtracted 12:00 with 8:00 (C6-D6), the result was 4:00 (E6). With this example, the wage should be \$8.00 (4 hours x \$2.00), but when I multiplied HOURS WORKED with HOURLY RATE (E6*A1), the result was 0.33. How do I get the right amount?
Thanks!
-John

• Veronica Mount

John,
Your formula for multiplying time worked times pay rate must also include multiplying by 24:
=hours worked * pay rate * 24
Veronica

4. John

Hi Veronica,

Follow-up question, how do I subtract minutes?
We give a 15min grace period for time in. So in this example:
TIME IN- 8:07
TIME OUT- 12:00
How do I subtract the 7 minutes so that it still computes as a full 4 hours of work?

-John

• Veronica Mount

John,
In the solution below I am using the serial number values for the times involved – .33333333 = 8:00 am, .01041667 = 15 minutes. So for the calculation where the start time is in cell A1, the end time is in B1 and the pay rate is in E1, the formula would be:
=(B1-IF(Value(A1)-0.3333333<0.01041667,0.3333333,A1))*E1*24
The Value function converts the time entered in cell A1 to its serial number value for comparison.
This formula checks whether the difference between the value in A1 and 8:00 (0.3333333) is less than 15 minutes. If so, it uses the value for 8:00 to subtract from B1's value. If not, it uses the actual value in A1 in the subtraction.
Veronica

5. John

It’s my first time to use the Value function. Thanks Veronica.

-John

6. Shiva

Veronica, Hi,

I am looking for a solution to mark/highlight ‘Duplicate’ text i a column using IF statement they may be list of many of 1000= names listed. Can you help me to sort this. thank you.

7. Angelina

Thank you for solving a long standing issue I had with our schedules when inputting out times that went past midnight.Educational institues are always 1 of the three elements in research of development of techology.Your contribution and other similar institues are proven source of information.We at Cellocean acknowledge and appreciate and always as supporting plateform to publish information.

8. Shiva

Hi, Veronica,

Can you help me on the issue of “too many cell formats” pop up message with in excel 2007, were i cant loose the existing data collected through the pervious months in analysis, Thank you for your help!

9. Worked for me too. Thanks Veronica.

10. Hi Veronica,

Im having trouble calculating the days, hours and seconds between two dates.
Can include only hours from 8:00 am to 5:00 pm (to maximum of 9 hours per day)
Exclude weekends and holidays

Like 1/31/2012 1:10:40 PM to 2/8/2012 4:35:59 PM

Can you help me to sort this. thank you.

Rick

11. Hi Veronica,

Find Duplicates in Excel without using Conditional Formatting.

One way to find duplicates on colum A using IF(,,):

Sort by colum A with “Headers”

On colum B “Sequency”
=IF(A2A1,1,B1+1)

To identify Duplicate on colum C “Duplicates ID”
=IF(B2>1,”Duplicate”,IF(A3A2,””,”Duplicate”))

To keep the last one and delete others
On Column D “Duplicates Count”
=IF(A3A2,B2,0)
Copy & Paste as values (Column B and D)
Delete column B
Sort by colum C
Delete values = 0 on colum C

Rick

12. Correction

On column b “Sequency”
=IF(A2A1,1,B1+1)

13. Melody

Veronica,

I am trying to figure out how to automatically calculate the average time elapsed between each time a compactor is emptied. I want to take all the dates a unit was emptied and calculate the average amount of time that has elapsed for all these dates. I’m trying to make it into a user friendly form that anyone can use just by typing in the date and having it automatically calculate the average time elapsed for management to use. Can you help me?

• Veronica Mount

Melody,
One approach to getting the values you want would be to set up your data in three columns with one holding the Compactor Emptied Date (in my example column A), one calculating Elapsed Time between that date and the previous date (my column B), and one calculating the Average of Time Between Empties (my column C).
In column B, I placed the formula: =A3-A2 [This would be the formula in cell B3 when the most recent date has just been entered in cell A3.]
In column C, I placed these nested functions: =AVERAGE(\$B\$3:OFFSET(C3,0,-1)) [Making cell B3 an absolute reference using the dollar signs means that as this is copied or extended down the rest of the column, cell B3 will always be the beginning of the range considered in the average. The OFFSET function makes the end of the range always the cell in column B in the row currently added.]
By making the entire range of data a Table, you cause the calculations to automatically extend to new rows as new dates are added in column A. You will probably need to set formats for columns B and C to get the display you want.The user just needs to click the cell in the next row at the bottom of the existing data, type in the date, and press ENTER or TAB.
I hope this helps you get the result you are looking for.
Veronica

14. I’ve read through all the posts and couldn’t find one that answered my specific question (I did try several of the formulas but they did not do what I wanted).

Is it possible to creat a timesheet in Excel for Time In, Time Out (lunch), Time In (lunch), Time Out using 12-hour format rather than 24-hour format? I have one that is currently working with staff entering their time in 24-hour format, but when I change the format to 12-hour, it doesn’t calculate their time properly.

Thank you!

Tina

• Veronica Mount

Tina,
But when they enter times in 12 hour format they are also entering AM or PM, right?
Could you send me some sample data?
Veronica

15. Veronica,

Oh sorry….no. They only enter the time. So they enter: IN 8:00 OUT 1:30 IN 2:30 OUT 5:00.

When they enter IN 8:00 and any time up to 12:59 OUT, IN 1:59, OUT 5:00 it calculates correctly. Once they enter an OUT that is 1:00 or later, it does not calculate.

I have the cells formated as [H]:mm.

The reason I am trying to make it work this way is because, beginning in January, they will also be utilizing a web-based time clock that shows time in 12 hour format. I’d like them to simply be able to transfer the time to their Excel timesheet without having to worry about changing to 24 hour format or adding AM/PM.

Tina

• Veronica Mount

Tina,
Your problem probably has to do with the fact that if you enter a time in a cell without indicating AM or PM, Excel assumes AM. So the person who enters 8:00 for IN and 1:00 for OUT would seem to have arrived at 8:00 AM and left at 1:00 AM. Excel wouldn’t know how to calculate the difference between the OUT time and the IN time in that case.
Veronica

16. Veronica,

Thank you! Sounds like the only way to do it is to have the users enter AM/PM when entering their time. I’m sure they can get used to that. 🙂

Thanks again,

Tina

17. Hi Veronica, Happy New Year.

Not sure if this is the right place to ask the question, but i am logging the amount of time a team spends on a task. The teams operate around the clock.
We log the start datetime & end datetime of the task however this can go from the end of one day and into the next.
Because we are logging as datetime there is no problem calculating duration, the problem is reporting activity per day.
Because the task can begin before midnight and end after midnight I am trying to work out how I report what time portion of the task was carried out on a specific day, I am trying to do this in a pivottable with a calculated field. Any suggestions? Thanks!

• Veronica Mount

Guy,
Here is one solution. I set up a sheet such that two rows serve each work time with one row pulling the date of the
shift’s START and calculating the hours worked on that date, and the next row pulling the date of the shift’s END
calculating the hours worked on that date. Even if all work is on the same date that’s OK.

I set up 5 columns: A = Start date and time, B = End date and time, C = Total hours of shift, D = just date
portion of start information in the first row for the shift, and E = calculation of hours worked on the date the
shift began. On the second row for the shift I entered in column D a formula to pull just the date portion of the
shift’s end and in column E I calculated the hours worked on that date.

So for the example of a shift with a start date and time of 2/2/12 20:00 and an end date and time of 2/3/12 4:00, I
set it up like this:
2/2/12 20:00 2/3/2012 4:00 8:00 2/2/2012 4:00
2/3/2012 4:00

The formula to pull just the date from the date and time is =TRUNC(A2) given the date and time in A2 and the format
of the cell set to Date. The formula to calculate the hours worked on the start day given the start date and time
in cell A2 and the end date and time in B2 is:
=IF(DAY(A2)=DAY(B2),B2-A2,1-(A2-TRUNC(A2)))
The formula to calculate the hours worked on the end day again assuming the start date and time in cell A2 and the
end date and time in B2 is:
=IF(DAY(A2)=DAY(B2),0,B2-TRUNC(B2))

With these calculations in place you can create a pivot table with the Date field as the Row Label field and the
Hours (column E) as the Value field – choosing to show the sum of hours.
I hope there is something here that you can use in your solution.
Happy New Year to you!!
Veronica

18. Maureen

I may have missed it in your previous posts but, I need to calculate months elapsed between two dates that are formatted as: yyyymmdd

i.e. How many months between 20061015 and 20120801?

• Veronica Mount

Maureen,
In my example I have the more recent date in cell B1 and the earlier date in cell A1 so the formula would be:
=(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)
The cell holding the formula needs to be formatted as a number.
Veronica

19. Maureen

I had tried that but, it returns a ‘#NUM!’ error. It seems confused by the year coming first?

20. Veronica Mount

Maureen,
Sorry, I forgot to address that in my first response. If you are indeed entering the dates as you show above: 20061015, Excel is not recognizing them as dates. You need to put / or – between the year, month and day. Otherwise Excel thinks you are using the serial number for a date that is further out in the future than Excel’s limit.
For instance the serial for today, Jan. 16,2013, is only 41290.
It is OK that the elements are in the order you have them. They just need the separation symbols.
Veronica

21. Riley

Hi Veronica, I am interested in capturing patient waiting time, in minutes. Is there a way to have excel enter a timestamp in column B1 with the current time if a external barcode is scannced into column A1? I would like to track patient wait times with as few keystrokes as posible. This would be repeated once the service is provided to generate the time difference.

I believe I can format the cells (formula) as needed once the data is captured to equal wait time in minutes. Is this possible?
Riley

22. Brian

“Hi Andy,
This is tough because while Excel has the NETWORKDAYS function that will calculate whole days between two dates, I know of no function that breaks that down into hours and minutes. Here’s an approach you might play with. It is many steps so maybe a macro could help. It seems you need to capture for each part of the calculation, the value (date and time) of day’s beginning and day’s end for each date in your range. If the employee began work at 9:30 on Jan. 16, 2011 and that date is sitting in cell A1, in another cell you could use one of the Rounding functions to calculate just the date portion of the starting information. Then if you add to that number the serial value for 17:00, quitting time that day, you have the start time and end time for that day which can be subtracted to see the number of hours and minutes worked that day. You could do the same process for the end date only you would be using the value for 8:00 am added to the date portion of the serial value of the end date. Once you have the amounts for each day, you can total them.
Does anyone else out there have another approach?”

This is the exact problem I need to get resolved as well… I used the NETWORKDAY to get a sum of hours but I still don’t understand your method of limiting 8 hours for the work days.

23. faham

i want know how to a specific time including how many times from a time list.
for example:if we get a team which start on 10:20, we can see that team engaged with 3 teams.those are
which team is enter in 10:10,10:15 and 10:20
start end
team 10:05 10:15
team 10:10 10:25
team 10:15 10:40
team 10:20 10:30
team 10:25 10:40
team 10:30 10:55
team 10:35 10:45
team 10:40 10:55
team 10:45 11:10
team 10:50 11:05

i try to solve this problem,but i cannot. if you can please build a logical test for count in excel

24. Lloyd

Hi,

I would like to know how to compute how many hours a man works if he had answered 55 calls and 16 emails in 8 hours…So for 55 calls how many hours did he spent and for the 16 emails how many hours did he spent which total to 8 hours.

Thanks,

Lloyd

25. Veronica,

Thanks for this guide! It took a bit of playing around, but it works!

Hopefully Microsoft added something to make this easier in Excel 13.

Thanks again,

Josh

If I am picking up a load of product based on the production per hour, for an example, a factory produce 754 gallons of juice per hour and a full load is 7540 that meant it will take the factory 10 hours to creat a load. The factory close between 2:00 am and 4:00 and again between 14:00 and 16:00. Is there a way that excel could help calculate the down time in pick up schedule sheet ?

27. Angel

need help!

why do i get a “FALSE” result with this formula?

=IF(AND(F1TIMEVALUE(“0:30:00″)),”RV”,IF(AND(F1TIMEVALUE(“0:51:00″)),”IE”,IF(F1=TIMEVALUE(“0:00:00″),”MV”)))

value of my D1 is 11:30 AM
value of my E1 is 12:20 PM
F1 is a FORMULA =E1-D1 and gives me an answer of 0:50:00, however, instead of giving me a result of “RV” it shows “FALSE” instead.. 🙁

28. Veronica,

I am needing a formula which will automatically advance a set number of seconds onto the previous time.
I need it to show in minutes, seconds, and tenth seconds.
If my start is at 00:00.0, I need increments of 2.5 seconds. So next should show 00:02.5, next 00:05.0, next 00:07.5, next 00:10.0 ……..etc

29. addendum: I would like to drag this formula down the same column and it automatically applies this formula, adding the 2.5 seconds to each cell. The 2.5 seconds is an example, I will need to change this value from time to time, but it will always include the tenth of a second.

30. Susan

Angel-
Sorry for the lateness of this response, but there seems to be a problem with your IF Statement. The way your IF statement is structured, it is doing what you have asked; none of the conditions are true, so you are getting a “FALSE” response. (Did you mean for the first condition to read >=.30?)

The AND is used when you want multiple conditions to be true simultaneously so not sure what you are wanting to do here. The way this is set up, the AND is really not necessary since the values of .30, .51, and .00 cannot be true at the same time for the same cell.

In this instance, the answer you are looking for should be FALSE since .50 is not listed as one of the “TRUE” conditions. The statement could read like this (the AND is not necessary):
=IF(F1=TIMEVALUE(“0:30:00″),”RV”,IF(F1=TIMEVALUE(“0:51:00″),”IE”,IF(F1=TIMEVALUE(“0:00:00″),”MV”,”FALSE”)))

Again, I am not sure if you mean to use >= or just =. However, by just using the = sign, you will get an “RV” response if F1 contains the value of .30.00 or an “IE” response if F1 contains the value of .51. If none of these 3 responses are true, you will get a “FALSE” response – or whatever you specify at the end of the nested IF function.

Hope this helps.

31. Susan

Dave,

Set a cell with your increment using this time format: mm:ss.0.
Format the result column with this same time format.

All you have to do is start with a value (in this case 00:00.0) and multiply by the increment value.
In cell B2 the formula would read: =B1+\$A\$1. Using the drag fill handle in lower right corner, drag the handle down as far as you want and the values will increment. If you want to change the increment, all you have to do is change it in cell A1.

A B
00.02.5 00:00.0
00:02.5
00:05.0
00:07.5

32. Susan

Dave,
Sorry – when I submitted my response, the columns squished together; Cell A1 has the increment value, and the 2nd column should look like this (with formula starting in B2):

00:00.0
00.02.5
00.05.0
00.07.5

33. I’m not very familiar with Excel functions. Can you help me determine the formula for adding 15 business days to a cell that contains a date? Also, what would the fomula need to be if I want the destination cell to be blank if the date cell is blank?

34. Susan

Pam,

=IF(A4=””,””,WORKDAY(A4,15,H1:H2))
where cells H1 and H2 are listing the holidays that are non-working days.

35. Shiva

Veronica,

Hi, I was looking to count number of <04:31:00, =00:00:00 hours spent in a month with below formula but the same is not working within pivot table when columns are inserted. Please can you suggest any solution?

=COUNTIF(B2:OFFSET(L2,0,-1),"=00:00:00")+(COUNTIFS(B2:OFFSET(L2,0,-1),"00:00:00″))*0.5

Thank you, Shiva

36. Tomekia

I created a table below and my supervisor would like for me to “add a metric on how many days are under 1:00 vs Over 1:00 as compared to the total number of days (as a percentage)”. Could you please assist me with this?

Date Max Delay

10/1/2013 0:00:41
10/2/2013 0:00:51
10/3/2013 0:00:23
10/4/2013 0:00:54
10/7/2013 0:02:04
10/8/2013 0:01:02
10/9/2013 0:00:12
10/10/2013 0:00:51
10/11/2013 0:00:14
10/14/2013 0:00:25
10/15/2013 0:02:30

37. Dalibor

Hello there. I need a little help as Im doing a quiz in excel and I would like to count time passed for each answer.Example: when somebody enter answer in B1, clock should start counting time in C1 from 00:00 (min:sec), and when it enter another answer in B2, time should be displayed in C2 how much it passed from time in C1 till the time pressed ENTER on C2. Same thing for answer in B3: in C3 should be difference between time in C2 and moment of presing ENTER (for an answer) in B3. So, I would need data how much time person spend on each question. Than at the end, total time spent on all questions. I hope I have made it clear, since english is not my native, I am trying to be specific as I can.
I hope there is a solution for my problem and looking forward for an answer.
Regards abd best whishes from Croatia!

38. Ankush

Hi Veronica

i am trying to make a sheet which required time mission like 20 of them but need to finish first one first than go to 2nd one out of 10 different items and its run for 24 hours when first mission finish 2nd start and goes on till 24th and i have 24 hours and 10 factories to make that happen on time value of each mission increase on each mission i never finish them on time so want to know if i can make a table which can calculate what should i make so i can finish stuff on time. 10 items are different value and build in various time.

39. Deepika

Hi, I need formula to calculate time once a number is copied from a cell.

40. I have read everything on this site but must have missed the answer to this question.
Using the Australian custom of date 23/4/09 and 24 hour time, What is the time difference in Day,Hour, Minute between the following
3/7/14 08:15 and 5/7/14 16:20.
Can you please explain the steps to get the result.

Thanks
John

41. Hi Veronica,

I have been searching a lot recently for time based formulas as i need to calculate processing time excluding weekends. Your article above is the best that I have come across explaining everything in simple terms. Well done.

Regards
Khalid

42. Hi, I am not really familiar with exel.
I am interested to convert spent time in hours that can be calculated as a normal number ( 37:30:00 to convert in 37,5 hour).

Thank you very much.

43. Ben Thom

Hi

I’m trying to work out if I have a value that represents ‘total number processed’ example ‘2000’. I know that it takes 15 minutes to process an application, how do I work out total time taken in a time format to process all 2000?

Been playing around with different formulas, can’t make it work!

Thank you
Ben

44. Vincent

Hi,
What is the average response time and display rate in excel?
Thanks.

45. thankyou Veronica. I’ll try this formula