Calculating time difference with different formats

**r_georg** · 02-11-2010, 04:09 PM

I would like to calculate a difference in seconds between two columns of timestamps
1. First one is "00:34:52" and formatted as "00:34:52" in Custom hh:mm:ss format.
2. Second is "12:34:55 AM" and formatted as "00:34:55" in Custom hh:mm:ss format.

I tried the recommended =TEXT(B2-A2,"ss") with different permutations in the formatting but to no avail.

Any suggestions are deeply appreciated.

**rwgrietveld** · 02-11-2010, 04:50 PM

you have to understand that time is indexed to 1 day
0 (zero) = 0
1 (one) = 1 day = 24 hours = 1440 minutes = 86400 seconds

If you want seconds than you need to multiply with 86400.

Set the cell format back to general as it is no longer time but a value with seconds as unit.

**r_georg** · 02-11-2010, 05:36 PM

Originally Posted by rwgrietveld

you have to understand that time is indexed to 1 day
0 (zero) = 0
1 (one) = 1 day = 24 hours = 1440 minutes = 86400 seconds

If you want seconds than you need to multiply with 86400.

Set the cell format back to general as it is no longer time but a value with seconds as unit.

I understand the day indexing but that does not help in this case.
Let me ask the question in a different way.
How can I adjust the following cell data "00:03:45" to represent for instance "02/10/2010 12:03:45 AM" so I can calculate a time difference?
TIA