Removing seconds from cell value

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

In my spreadsheet there is a cell that is in time format
type hh:mm. If I write to this cell eg. 13:00 it looks
13:00 but the actual value is 13:00:00. How I can get rid
of those seconds in the actual value?

I have tried all sort of formats: time, date, custom but I
just can't get rid of those seconds.

All help is highly appreciated!
 
Hi Roger!

The seconds will always be there in the formula bar as they represent
the number stored by Excel. Your formatting only affects how the
number is displayed and not what is stored.

However if you want to round actual times to the nearest minute, you'd
have to use a helper column and round using:

=ROUND(A1*(24*60),0)/(24*60)
Format as time

But if you did:
Copy > Edit > Paste Special > Value > OK

You'd find that the seconds are still there. I suppose the analogy is
that even if you remove the second had from a clock you still don't
stop the seconds.

--
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