format colon

[Pammy]

I am doing an overtime spreadsheet, and when a user types in 08:00 this is
the way it looks, I have it formatted to custom [hh:]mm. This works good
until someone types in 0800 and it puts in 19200:00. Is there a format that
will allow a user to type in 0800 and it put the correct number of 08:00?

 

[David Biddulph]

You may want to use Data Validation and test for a value less than 1, then
give an appropriate message to the user if it isn't.

 

[Gord Dibben]

You cannot format a cell so that 0800 will return 08:00 as a valid time.

Excel doesn't do "masks" for date and time.

You can use VBA to for that if you want to go that route.

See Chip Pearson's site for code if interested.

http://www.cpearson.com/excel/DateTimeEntry.htm


Gord Dibben MS Excel MVP