AM/PM Format

  • Thread starter Thread starter bgcooker
  • Start date Start date
B

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?
 
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.
 
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.
 
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.
 
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
 
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.
 
.... 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.
 
Back
Top