Having a problem formatting short time

  • Thread starter Thread starter clueless
  • Start date Start date
C

clueless

I am having a problem formatting time in my spreadsheet. If I type in a
number (ex. 1300) and then try to format the cell this is what happens ....
the cell changes to 00:00 and the formula bar shows 7/23/1903 12:00 AM. Can
anyone help me? I give up!
--
 
I am having a problem formatting time in my spreadsheet. If I type in a
number (ex. 1300) and then try to format the cell this is what happens ....
the cell changes to 00:00 and the formula bar shows 7/23/1903 12:00 AM. Can
anyone help me? I give up!
Click to expand...

Formatting does not affect how Excel interprets your date or time entry.

If you type 1300 into a cell, and then format it as a time, Excel will
interpret that as 1300 days (and no hours) since 1/1/1900. That happens to be
7/23/1903 12:00 AM

If you want to enter 1300 as a time, you must enter the ":". eg. 13:00.

There are methods of entering 1300 and having Excel convert it to 13:00, but
you need to use VBA to do that. See
http://www.cpearson.com/excel/DateTimeEntry.htm
--ron
 
Back
Top