Subtracting Time using mm:ss.00 format

[Miasha]

I need to subtract the difference between two different times. For example:
1) In Cell A1 I have typed 03:24.65
2) In Cell B1, I have typed 05:12:55
I formatted the cells as: mm:ss.00.
My formula in C1 is =A1-B1. It returns all pound signs (#######).
Additionally, when you click in the enter field for cell A1 it displays
12:03:25 AM instead of what I typed in A1 which was 03:24.65. I must have
cell C1 display the exact difference. Can anyone tell me the best function
or IF statement to use to calculate this? Thanks in advance.

 

[Dave Peterson]

First, ignore the formula bar. You're not going to change that.

Second, after you subtract the larger from the smaller, you're going to end up
with negative time.

One way to show negative time is to change to the 1904 date system
(tools|Options|calculation tab|check 1904 date system)

But every date in your workbook will be off by 4 years and one day--and if you
copy|paste dates between workbooks with different date systems, you'll want to
fix the problem.

Saved from a previous post:

One workbook was using a base year of 1900 and the other was using 1904.
(tools|options|calculation tab|1904 date system)

One way to add those four years back is to find an empty cell, put 1462 into
that cell.

Copy that cell.

Select your range that contains the dates.
Edit|PasteSpecial|Click Add (or Subtract) depending on which workbook you want
to fix.

You may have to reformat the cell as a date (mine turned to a 5 digit number).
But it should work.

You may want to do it against a copy...just in case.

Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as
the base date.

 

[Conan Kelly]

Miasha,

=A1-B1 is subtracting the larger from the smaller, meaning the results will
be negative. EL can't handle negative times. That is why you are getting
the #####. Reverse the cell references and you calculation should work
correctly: =B1-A1

A1 = 03:24.65 = 3 mins, 24 secs, 65 ms. As far as XL is concerned about
time, 12 midnight is 0, high noon is 0.5....so 03:14.65 would round to
12:03:15.
A2 = 05:12:55 = 5 hrs, 12 mins, 55 secs.

HTH,

Conan

 

[Dave Peterson]

You can get negative times and negative dates if the workbook uses the 1904 date
system.

 

[Miasha]

Thanks so much! This helped tremendously!

First, ignore the formula bar. You're not going to change that.

Second, after you subtract the larger from the smaller, you're going to end up
with negative time.

One way to show negative time is to change to the 1904 date system
(tools|Options|calculation tab|check 1904 date system)

But every date in your workbook will be off by 4 years and one day--and if you
copy|paste dates between workbooks with different date systems, you'll want to
fix the problem.

Saved from a previous post:

One workbook was using a base year of 1900 and the other was using 1904.
(tools|options|calculation tab|1904 date system)

One way to add those four years back is to find an empty cell, put 1462 into
that cell.

Copy that cell.

Select your range that contains the dates.
Edit|PasteSpecial|Click Add (or Subtract) depending on which workbook you want
to fix.

You may have to reformat the cell as a date (mine turned to a 5 digit number).
But it should work.

You may want to do it against a copy...just in case.

Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as
the base date.