# Thread: Calculating hours between dates and times (2003)

1. ## Calculating hours between dates and times (2003)

I have dates and times in a ss in a single column using the format of mo/da/yr. hh.mm. I would like to be able to find the difference between these entries in hours (or hours and minutes). Is this possible or do I need to have separate columns for time and date?

A
1 3/11/04 19:00
2 3/12/04 7:30
3 3/15/04 12:30

2. ## Re: Calculating hours between dates and times (2003)

You don't need separate columns, just format the =end_date&time - start_date&time calculation using

[h]:mm:ss

(':ss' is optional)

3. ## Re: Calculating hours between dates and times (2003)

John,

Doesn't work. For example the difference between the last two entries in my example returns 5:00

4. ## Re: Calculating hours between dates and times (2003)

Since you had three data items, I'm not sure what your calculation is, but this works for me:
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><tr><td align=center>1</td><td align=right>3/11/2004 19:00</td><td>[h]:mm</td><td align=right>

5. ## Re: Calculating hours between dates and times (2003)

This is what I have and what I get. Column B is formatted with h:mm

A B

3/13/04 16:00
3/14/04 19:00 3:00
3/15/04 6:00 11:00

I have B2 formula =A2-A1
I have B3 formula =A3-A2

B3 gives the correct answer but B2 does not.

6. ## Re: Calculating hours between dates and times (2003)

Select the column B cells, then select Format, Cells, Number tab, Custom and edit the format to [h]:mm.

7. ## Re: Calculating hours between dates and times (2003)

As an explanation of the difference between [h]:mm and h:mm

h:mm is "time in a day" and and can only be in the range 0-23, since the 24th "hr" is the next day.[h] is elapsed time and can go over 24 hours.

Similarly if you want elapsed minutes you can use [m]:ss or even get elapsed seconds [s]. without the brackets the minutes and secs will not go above 59.

without the elapsed time, you are getting that i is 3 days and 5 hours later, but you are telling excel to only display the "hours" (=5) and ignore the "day portion"

Steve

8. ## Re: Calculating hours between dates and times (2003)

John,

Thanks for hanging in there. You kept telling me the answer and I was just to DENSE to see the [h]: vs. h:. Got it now. Thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•