AM/PM Format

[bgcooker]

I want to be able to enter a time as a 12-hour time in one column of cells
and have the time entered as formatted as AM. I want to do the same in
another column of cells and have those cells format the time as PM. How do I
accomplish this?

 

[Dave Peterson]

Either enter the times using the PM
1:00:00 PM
or use a 24 hour clock:
13:00:00

If you use a 12 hour clock and don't give excel any AM/PM information, it'll
assume you mean AM.

 

[Fred Smith]

There's no problem with the AM time. You can enter hh:mm, and format it the
way you want.

With the PM time, if you don't need to use the number as a time, you can use
a custom format of:
hh:mm "pm"

If you need to work with the data as a time, then you would need to add 12
hours to the PM time, either with a macro or a helper column.

Regards,
Fred.

 

[YCC (Graham)]

Hi BG,
If you have unformatted cells just type 10 am or 10 pm into the cells aming
sure you have a space between the nymber and the am/pm. Excel will then use
this format code "hh:mm AM/PM: which you can find in the Format/Cells/Number
Menu. It works a treat and formats the cells as you go.
No need to do anything else and the numbers can be used in calculations.

 

[bgcooker]

Thanks much for your help. I'm closer now to what I need. Can you explain
how to perform the adding 12 hours via a helper column? Thanks

 

[Fred Smith]

You would type, say, 9:32 into cell b2. In the c2, use the formula:
=if(b2="","",b2+time(12,0,0))
and format the cell as you prefer (like h:mm am/pm)

Copy the formula down, and use column C in all your calculations.

Regards,
Fred.

 

[David Biddulph]

.... or just +0.5 (as 12 hours is 0.5 of a day).

You can either add 0.5 in the formula, or put 0.5 in a spare cell and copy
it, then select the column of data to which you want to add 0.5, and Edit/
Paste Special/ Add.