Excel.Tips.Net ExcelTips (Menu Interface)

Working with Elapsed Time

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Working with Elapsed Time.

Excel allows you to enter many things into a cell, including times. This leads some people to use Excel to help calculate elapsed times. For instance, you may have a column that contains a starting time, another that contains an ending time, and then use a third column to calculate the time between the beginning and ending time.

In such situations, you may be tempted to simply put the following as your formula in the cells of the third column:


This will work fine, provided the time shown in B2 is always later than the time shown in A2. However, you will get erroneous results if the times do not fit this rigid stricture. What do you do if you are running operations around the clock, and it is just as likely that the value at A2 will be just before midnight and the value in B2 will be after midnight?

One solution is to make sure that you always enter dates with your times. If you do this, then B2 will always be later than A2. For many people, however, this is a bother. If you find yourself simply entering the time, then you are still up the creek.

If you enter your times without an AM/PM designation using a 12-hour clock, then you should use the following formula in the cells of the third column:


This formula uses the IF function to determine if the beginning time is later than the ending time. If it is, then .5 (which is half a day, or 12 hours) is added to the ending time before the subtraction is done. If the beginning time is earlier then the ending time, then a normal subtraction operation is done.

If you use an AM/PM designation in your times, or you enter information using a 24-hour clock, then the formula must change just a bit. Now it should appear as follows:


Now, instead of adding only half a day, you are adding a whole day (24 hours) to the ending time. This, again, provides the proper result.

It is interesting to note that in both of these instances, Excel allows you to add hours and minutes, if desired. While the above example is clean and simple, the following could also have been used:


Regardless of the formula you use, once it is entered you need to make sure the cell with the formula is formatted to properly show elapsed time. You do this by following these steps:

  1. Choose Cells from the Format menu. Excel displays the Format Cells dialog box. (See Figure 1.)
  2. Figure 1. The Format Cells dialog box.

  3. In the Category list (left side) choose Time.
  4. In the Type box, choose 37:30:55 as the type.
  5. Click on OK.

Your cell now shows the correct elapsed hours, minutes, and seconds.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2819) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Working with Elapsed Time.

Related Tips:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

Michael (Micky) Avidan    20 Mar 2016, 08:04
If all the 21,000 cells are adjacent to each other (meaning with no empty cell(s) in between) then you task is only a Double-Click away.
1) In cell D1 type: =MOD(C1-B1 ,1) and hit Enter.
2) Hover your mouse cursor over the lower right corner of cell D1 and when the cursor turns into a small black cross Double click the mouse's left button.
Et Voilà !
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
Michael (Micky) Avidan    20 Mar 2016, 07:54
@@@ To whom it may concern,
The shortest formula to handle "Normal" working hours and also the (Start time) is before midnight and the (End time) is after midnight - is:
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
Jay    19 Mar 2016, 21:30
Good evening.
I have pulled data from a program, and have two columns B and C, one with a star time, and a second with an end time. I know can enter "=C1-B1" and receive the number of hours and minutes (h:mm), in column D, but I'd like to do it to approximately 21,000 start and finish times. Is there any way to select an entire column (ex. Column D) and have it subtract times C from B, and have it display on each individual row? I'm trying to figure out a time-workload for each And every entry. Just not too excited about manually typing formulas for all 21,000 rows.
Barry    03 Jun 2015, 05:57

I suggest you use conditional formatting for this task. If I assume your table starts from row 2 with headers in row 1, and that you timestamp is in column F.

The following will highlight every row where the timestamp is older than 1 week ago.

1. select the whole of the table except row 1 starting from cell B2,
2. open the conditional formatting dialogue box (Format-> Conditional Formatting...)
3. in the drop down list select "Formula Is"
4. in the condition box enter the required condition ensuring that there is a "$" in front of the column "F" e.g. =Now()-7>$F2
5. choose your required formatting, and click OK.

Ryan    02 Jun 2015, 09:29
I have a file that have time stamps and i would like to automate if a line would go over a certain threshold. I would like to know if we can automate this and whenever i open the file it will automatically show me lapsing lines
Michael (Micky) Avidan    17 Jan 2014, 07:23
If you care about displaying the result in Hour format - omit the 24 multiplication and custom format, the result cell, as [h]:mm
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
Michael (Micky) Avidan    17 Jan 2014, 07:17
The, suggested "Array Formula", in the attached picture, will return the time interval (in decimal hours), between two given dates, EXCLUDING Saturdays+Sundays.
As for the holidays - you will have to build a list of them (can be Exported from "Outlook") and deduct, from the formula, the count of them (between the two given dates).
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
Michael (Micky) Avidan    17 Jan 2014, 06:36
For all who may concern - there is no need for any IF statement nor for such "huge" formulas.
In BOTH(!) cases, when: A2<B2 or A2>B2 the following formula will return the correct time gap:


Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
Gill    16 Jan 2014, 11:26
Hi, I'm trying to calculate elapsed time in months over a 12 year interval, I'm using "=A1(11/17/13)- NOW())/30" which returns 144 months. My question is why doesn't it return 143 months, as todays date is 1/16/14? By tomorrow shouldn't it be down to 142 months? I've read a few of your articles so I just checked the formatting of the results cell, it's "Custom 0_)" but even if I change the formatting to Number zero decimal places I get 144. If I go Number two decimal places I get a result of 143.75, shouldn't it be approximately 142.1 or something?

Any help will be appreciated.

Keri    13 Jun 2013, 15:29
I am trying to calculate time (in hours) from one date to the next, excluding weekends or holidays. For example: I have a file that was submitted on 5/15/2013 at 9:55am and it wasn't claimed until 5/30/2013 at 5:17pm. I need to know how many hours this is in between the two time minus holidays and weekends.

brandon pitre    16 Aug 2012, 12:46
So I followed your instructions to a T and the formula you provided is only producing a TRUE result. Not posting the elapsed time.

All cells are formatted properly.

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2017 Sharon Parq Associates, Inc.