Time Arithmetic

[Dan Chupinsky]

I copied a list of data from the web for further analysis. One of the
columns contains a time duration and is shown as 3:04:22, meaning 3 hours, 4
minutes and 22 seconds. I formatted the column as time [37:30:55] 6th pick
on the default list.

When I try to perform time arithmetic on the data, trying to calculate the
difference between two time values [=a2-a1] for example, I get an error
#VALUE as a result. What am I doing wrong?

Dan

 

[Kieran]

Try formatting it as a custom type and enter
[h]:mm:ss as the format.

 

[Earl Kiosterud]

Dan,

There's a good chance you have text in the cells, not true Excel dates.
Extra spaces will foul it up too (look in the formula bar for spaces). If
your dates won't respond to changes in date formatting (Format - Cells -
Date - xxx), then they're text, and you can't do math on them directly.

 

[Dan Chupinsky]

Earl,

Thanks much that did it. There was a space at the beginning of the data that
I was not aware of it being there.

Dan

Dan,

There's a good chance you have text in the cells, not true Excel dates.
Extra spaces will foul it up too (look in the formula bar for spaces). If
your dates won't respond to changes in date formatting (Format - Cells -
Date - xxx), then they're text, and you can't do math on them directly.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

I copied a list of data from the web for further analysis. One of the
columns contains a time duration and is shown as 3:04:22, meaning 3

hours,

4
minutes and 22 seconds. I formatted the column as time [37:30:55] 6th pick
on the default list.

When I try to perform time arithmetic on the data, trying to calculate the
difference between two time values [=a2-a1] for example, I get an error
#VALUE as a result. What am I doing wrong?

Dan