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

  • Thread starter Thread starter nave
  • Start date Start date
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
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
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
 
Hi Nave!

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

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top