I want to subtract 57 sec. from 1 minute 27 sec. and input 1:27 not 87 sec. help!

N

nave

This is what I do now

A1=57
B1=87...... But I want B1 to look like this-1:27

=(B1-A1)

Please let me know the right formula. Thank
 
N

Norman Harker

Hi Nave!

You seem to be wanting to have your data in time format but are not
"telling" Excel that. How does Excel "know" that 57 is 57 seconds?

in A1:
=57/(24*60*60)
Format as time (e.g.) h:mm:ss
Returns 00:00:57

in B1
=87/(24*60*60)
Format as time
Returns 0:01:27

Now =B1-A1 returns 0:00:30

Excel records time as a decimal part of 1 day hence to get seconds
from bare numbers we divide by 24*60*60

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

Norman Harker

Hi Nave

As an alternative to doing a calculation you can input times and
Intellisence will format as times

A1 Entry 0:0:57
B1 Entry 0:1:27

B1 entry could be 0:0:87 but then Intellisence doesn't step in and
format as time but gives 0.00100694444444444 which formatted as time
is 0:1:27



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

nave

Norman thanks

My problem is, at work I have excel but at home I have a microsoft
works spreadsheet and the time thing does not work. I'll have to get
excel at home also
thanks for the info
 
N

Norman Harker

Hi Nave!

Your office should be buying you the software to work at home <g>

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top