Column with Time-only data (1899-12-30 ...) won't reformat

IMNumberFourteen

New Member
Joined
Dec 9, 2014
Messages
2
Hello experts,

I have a column of data that I imported from Excel into SQL Server.
The Excel format was 6:45:00 PM.
It was imported into the SQL table in the format 1899-12-30 18:45:00.000

Now, however, when I pasted the data back into Excel and try to format it as 24 hr time (16:45), Excel does nothing. I have tried the 'Time 13:30' format as well as a Custom format of HH:MM with no luck. The Excel column data remains 1899-12-30 18:45:00.000.

Does anyone know why Excel won't reformat that column when I paste it back into Excel?

Thanks for any help!

- IMNumberFourteen
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
EDITED TO OFFER A MORE ACCURATE RESPONSE

Excel only recognizes date/times ON or AFTER midnight on Dec 31, 1899

Enter 0 in a cell and format it as a date/time to see the result.

Date/times prior to that are treated as text.
 
Last edited:

IMNumberFourteen

New Member
Joined
Dec 9, 2014
Messages
2
EDITED TO OFFER A MORE ACCURATE RESPONSE

Excel only recognizes date/times ON or AFTER midnight on Dec 31, 1899

Enter 0 in a cell and format it as a date/time to see the result.

Date/times prior to that are treated as text.
Great, that explains it!

Much appreciated.

- IMNumberFourteen
 

Watch MrExcel Video

Forum statistics

Threads
1,095,984
Messages
5,447,700
Members
405,463
Latest member
Tommy5

This Week's Hot Topics

Top