Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
AM/PM Format
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
AM/PM Format
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. bgcooker wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
AM/PM Format
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. "bgcooker" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
AM/PM Format
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" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
AM/PM Format
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" wrote: 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. "bgcooker" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
AM/PM Format
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. "bgcooker" wrote in message ... 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" wrote: 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. "bgcooker" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
AM/PM Format
.... 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. -- David Biddulph "Fred Smith" wrote in message ... 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. "bgcooker" wrote in message ... 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" wrote: 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. "bgcooker" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock Cell Format - Allow copy and paste of data without format change | Excel Worksheet Functions | |||
Adding time in 24 hour format to produce hours in decimal format | Excel Worksheet Functions | |||
Excel 2003. Custom format gets replaced by Special format. | New Users to Excel | |||
Replace million-billion number format to lakhs-crores format | Excel Discussion (Misc queries) | |||
how to format excel format to text format with separator "|" in s. | New Users to Excel |