Time calculus in Excel 2000

  • Thread starter First Aid Computing
  • Start date
F

First Aid Computing

Greetings. I have recently been handed a project dealing with process times.
I have one set of numbers as seconds (ss.ssssssss) and one as a decimal
version of minutes (.91 m). What process do I need to know to convert these
numbers into a workable format. I'm hoping to have them as mm:ss.ss and then
doing basic math with the results. Thanks in advacne.
 
R

Rick Rothstein

In Excel, Time is the fraction of a day that has passed for the given time
(for example, 3:00 AM is 3/24 (or 0.125) of a 24-hour day.

To convert seconds (assumed to be in A1), use this... =A1/86400

To convert minutes (assumed to be in B1), use this... ==B1/1440

Custom Format the cells using this pattern... mm.ss.00 in the Type field.

Note: The 86400 comes from multiplying 24 (hours in a day) times 60 (minutes
in an hour) * 60 (seconds in a minute); the 1440 comes from multiplying 24
(hours in a day) times 60 (minutes in an hour).
 
R

Rick Rothstein

In Excel, Time is the fraction of a day that has passed for the given time
(for example, 3:00 AM is 3/24 (or 0.125) of a 24-hour day.

To convert seconds (assumed to be in A1), use this... =A1/86400

To convert minutes (assumed to be in B1), use this... ==B1/1440

Custom Format the cells using this pattern... mm.ss.00 in the Type field.

Note: The 86400 comes from multiplying 24 (hours in a day) times 60 (minutes
in an hour) * 60 (seconds in a minute); the 1440 comes from multiplying 24
(hours in a day) times 60 (minutes in an hour).
 
F

First Aid Computing

This works well. Now to figure out how to have it display negative seconds
as - instead of ############.
 
D

David Biddulph

Tools/ Options/ Calculation: 1904 date system
but beware if you need to transfer data to and fro between 1900 and 1904
date systems.
 
F

First Aid Computing

Afraid that the 1904 seems to do nothing. I just want to subtract two time
amounts and have it display a negative and the time. Eg. 25.3s - 26.3s =
-1.0s. Right now it shows ############ or if I reformat to general,
-1.15741-05.
 
D

Dave Peterson

I'd try it once more.
Afraid that the 1904 seems to do nothing. I just want to subtract two time
amounts and have it display a negative and the time. Eg. 25.3s - 26.3s =
-1.0s. Right now it shows ############ or if I reformat to general,
-1.15741-05.
Click to expand...
 
F

First Aid Computing

Well, I'll be horn-swaggled. It decided to work tonight. It didn't at
first, so I changed the number format then changed it back and it worked on
the entire sheet now. Thank you very much.
 
Top