EXCEL / Time - Adding/Subtracting 1

[jrobin5881]

I've developed a spreadsheet comparing travel times. I have the time from point A to point B listed in one column. I have time from a different point A, to the same point B listed in another column. The time is formatted as h:mm for each of the cells. At the bottom of each of the two columns I have a summation cell i.e. =SUM(F3:F54) and formatted as [h]:mm.

I am getting the wrong column totals when I use the formula above, Why? (it is less then what I get when I add it manually)

 

[SkipVought]

Hi,

Please post examples of the time values that do not sum as expected.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jrobin5881]

I found that when I change the summation cell from [h]:mm to h:mm I get the right answer
0:17
0:06
0:26
0:47
0:17
0:29
0:22
0:35
0:32
0:26
0:17
0:15
0:36
0:00
0:32
0:29
0:09
0:27
0:24
1:01
0:12
0:00
0:24
0:08
0:18
0:29
0:26
0:47
1:01
0:25
0:29
0:26
1:01
0:29
0:29
0:00
0:40
0:27
1:01
0:15
1:00
0:29
0:00
0:29
0:18
1:40
0:03
0:16
0:27
0:37
0:15
0:09
= 23:37 when set to h:mm
= 47:37 when set to [h]:mm

 

[SkipVought]

I get exactly 23:37 when set to [h]:mm

Check to see if you have some hidden rows containing data.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jrobin5881]

Skip,

Nope, no hidden rows and not sure that would account for it anyway because I'm just changing the format of the sum cell. I Can't figure it out? making me nuts.

 

[SkipVought]

What happens if you change the cell format from h:mm to GENERAL - what VALUE do you see in this cell?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

...and what is the FORMULA in this cell?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jrobin5881]

1.984583333

 

[SkipVought]

OK,

You have your range of times FORMATTED to show h:mm.

HOWEVER, you ALSO HAVE DAYS, I would BET! (ie values GREATER THAN 1)

faq68-5827





Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Observe: Change the formatting on the range of times to GENERAL and see values greater than ONE.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jrobin5881]

Skip,

Yes, I have some locations that take over an hour to get to(1:01, 1:40) etc. I read your link on time but was not sure what to do to correct.

Should I parse out the hours to it's own column and just calc the minutes?

 

[SkipVought]

Are you not calculating TOTAL DURATION?

In that case the DURATIONS that are greather than 1 are durations that are more than 24 hours. If you FORMATTED your Time Range as [h]:mm, you would 'see' that this was the case.

This IS indeed, what the [h] format is supposed to do -- show you the hours of duration even when they exceed 24. The h formatt ONLY indicates TIME, not DURATION.

Do you understand the difference?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jrobin5881]

Disregard my last post... I did what you said and found the culprit!!! This is an excellent tip, thanks so much