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

=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**.

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

Want to find out how many of a particular weekday occur within a given month? Here's how you can find the desired ...

Discover MoreFor many Excel users—particularly beginners—working with elapsed time can be bewildering. This tip explains ...

Discover MoreExcel allows you to perform quite a few operations using dates in your worksheet. Sometimes, however, the answer may not ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2016-03-20 08:04:08

Michael (Micky) Avidan

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

2016-03-20 07:54:46

Michael (Micky) Avidan

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

2016-03-19 21:30:23

Jay

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.

2015-06-03 05:57:16

Barry

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.

2015-06-02 09:29:25

Ryan

2014-01-17 07:23:05

Michael (Micky) Avidan

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

2014-01-17 07:17:29

Michael (Micky) Avidan

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

2014-01-17 06:36:31

Michael (Micky) Avidan

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

2014-01-16 11:26:06

Gill

Any help will be appreciated.

Gill

2013-06-13 15:29:10

Keri

HELP!

2012-08-16 12:46:53

brandon pitre

All cells are formatted properly.

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.

**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Copyright © 2018 Sharon Parq Associates, Inc.

## Comments