Loading

Got a version of Excel that uses the
menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)?
**This site is for you!** If you
use a later version of Excel, visit
our *ExcelTips* site focusing on the ribbon interface.

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.

Learn more about Allen...

ExcelTips FAQ

ExcelTips Resources

Ask an Excel Question

Make a Comment

Free Business Forms

Free Calendars

** 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),

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:

=B2-A2

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:

=IF(A2>B2,(B2+0.5)-A2,B2-A2)

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:

=IF(A2>B2,(B2+1)-A2,B2-A2)

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:

=IF(A19>B19,(B19+"24:00")-A19,B19-A19)

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:

- Choose Cells from the Format menu. Excel displays the Format Cells dialog box. (See Figure 1.)
- In the Category list (left side) choose Time.
- In the Type box, choose 37:30:55 as the type.
- Click on OK.

** Figure 1.** The Format Cells dialog box.

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:*

**Comprehensive VBA Guide** Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out *Mastering VBA for Office 2010* today!

@Jay,

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)

ISRAEL

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)

ISRAEL

@@@ 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:

=MOD(B2-A2,1)

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

The shortest formula to handle "Normal" working hours and also the (Start time) is before midnight and the (End time) is after midnight - is:

=MOD(B2-A2,1)

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

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.

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.

@Ryan,

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.

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.

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

@Keri,

If you care about displaying the result in Hour format - omit the 24 multiplication and custom format, the result cell, as [h]:mm

http://jpg.co.il/download/52d9200595b52.png

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

If you care about displaying the result in Hour format - omit the 24 multiplication and custom format, the result cell, as [h]:mm

http://jpg.co.il/download/52d9200595b52.png

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

@Keri,

The, suggested "Array Formula", in the attached picture, will return the time interval (in decimal hours), between two given dates, EXCLUDING Saturdays+Sundays.

http://jpg.co.il/download/52d91f263dbdd.png

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)

ISRAEL

The, suggested "Array Formula", in the attached picture, will return the time interval (in decimal hours), between two given dates, EXCLUDING Saturdays+Sundays.

http://jpg.co.il/download/52d91f263dbdd.png

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)

ISRAEL

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:

=B2-A2+(B2<=A2)

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

In BOTH(!) cases, when: A2<B2 or A2>B2 the following formula will return the correct time gap:

=B2-A2+(B2<=A2)

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

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.

Gill

Any help will be appreciated.

Gill

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.

HELP!

HELP!

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.

All cells are formatted properly.