# Calculating time difference with different formats

1. ## Calculating time difference with different formats

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.  Register To Reply

2. ## Re: Calculating time difference with different formats

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.  Register To Reply

3. ## Re: Calculating time difference with different formats 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  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 