Changing 1,2,3,4... into 1:00 AM, 2:00 AM, etc.

glinds

New Member
Joined
Jul 22, 2010
Messages
6
I have a list of numbers, 1-24, that I want to turn into times (AM and PM), but when I try to use the Format Cells: Time function for it, it automatically puts a date of 1/1/1900 in the front to where all of my numbers show 12:00AM. How do I get the times without dates?

Thank you!
Linds
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

delaneyjm

Well-known Member
Joined
Apr 22, 2009
Messages
624
You will need to divide the number by 24.

Times in Excel are represented by decimal numbers. e.g. noon is 0.5, 6:00 p.m. is 0.75

Then you can format it using the string h:mm:ss AM/PM.
 

Trouttrap2

Well-known Member
Joined
May 11, 2010
Messages
610
You could try this rather than custom formatting. In an adjacent column enter this: =TIME(A3,,) and drag down.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,325
Messages
5,528,023
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top