Format Times in AM/PM

BCS2

Board Regular
Joined
Dec 8, 2003
Messages
67
Is there a way to format a cell to automatically drop in the AM/PM designation based on the time entered?

For example, our hours of business are 8 AM - 5 PM. I would like to have all times between 8:00 and 11:59 designated as AM and all times between 12:00 and 5:00 as PM.

Thanks All

:rolleyes:
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Quickest way I can think of is to drop a bit of code on the Worksheet's Change Event handler. This example assumes times are being entered in column A. Just change the target column to suit your needs.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">If</SPAN> Target.Count <> 1 _
    <SPAN style="color:#00007F">Or</SPAN> Target.Column <> 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target < TimeSerial(8, 0, 0) <SPAN style="color:#00007F">Then</SPAN> Target = Target + 0.5
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

[Edit]I interpreted your request to be that you were hand-keying in times and didn't want to have to key in the PM bit (or use military time) for each entry. Apologize if I misunderstood. [EndEdit]
 
Upvote 0
Select your cell(s)
Format menu | Cells | Number tab | Custom
Scroll down until you find: “h:mm AM/PM” (this a standard custom format)
OK.

If you can’t find it, then create it. Do step 2 above, then in the box headed “Type”, enter
“h:mm AM/PM” (no quotes)
OK

Regards,

Mike
 
Upvote 0
Greg Truby said:
Quickest way I can think of is to drop a bit of code on the Worksheet's Change Event handler. This example assumes times are being entered in column A. Just change the target column to suit your needs.

[Edit]I interpreted your request to be that you were hand-keying in times and didn't want to have to key in the PM bit (or use military time) for each entry. Apologize if I misunderstood. [EndEdit]

You interpreted correctly, but how do I access the worksheet's change event handler? I also don't see where you have specified column A in your code.

Thanks :pray:
 
Upvote 0
Easiest way is to right-click the tab at the bottom of the worksheet in question. You should get a short popup menu. On that menu should be an option for View Code (normally it's the last option). Click on View Code and then just paste in the code above.

Column "A" is the same as Column 1. So the bit that reads: Or Target.Column <> 1 is what is checking for column A. If your times are in column B, change the "1" to a "2", if in column Z, change the "1" to "26" and so forth. Also, you may want to avoid processing header row(s). You can do this by adding a condition that check Target.Row. For example, the following is how the IF statement would look if Times are in Column G, and you have two (2) header rows:

Code:
If Target.Count <> 1 _
Or Target.Row <= 2 _
Or Target.Column <> 7 Then Exit Sub

Regards,
 
Upvote 0
What if the times are being entered into multiple columns?

Does this require separate code for each column or can I designate multiple columns in the same subroutine?
 
Upvote 0
On routine, but for better clarity in reading the code, probably better to split the IF into two pieces:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">If</SPAN> Target.Count <> 1 _
    <SPAN style="color:#00007F">Or</SPAN> Target.Row <= 2 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, Union([B:C], [F:F], [I:M])) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    <SPAN style="color:#00007F">If</SPAN> Target < TimeSerial(8, 0, 0) <SPAN style="color:#00007F">Then</SPAN> Target = Target + 0.5

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

The above would only execute on columns B, C, F, I, J, K, L, and M.
 
Upvote 0
Here is the code I have so far and a sample of my data. It' giving me a "Compile error: Argument not optional" Something to do with the Union command.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count<> 1 _
Or Target.Row<= 2 Then Exit Sub

If Intersect(Target, Union([D:H])) Is Nothing Then Exit Sub

If Target< TimeSerial(8, 0, 0) Then Target = Target + 0.5

End Sub

By the way, I have 2 header rows on my Excel sheet, I just didn't get copy that row to my sample data.
Estimate tracking.xls
DEFGH
2E-maildate/timePrintoutdate/timeDuedate/timeStartdate/timeDate/Timedelivered
31/6/049:42AM1/6/049:45AM1/6/045:00PM1/6/0410:49AM
41/6/049:41AM1/6/049:45AM1/6/045:00PM1/6/0412:00PM
51/6/041:38PM1/6/041:49AM1/7/0412:00PM1/6/042:40PM
Estimate Tracking


Again, I am looking for code that will automatically assign an AM/PM designation a cell based on the time entered.

Thanks for your patience.
 
Upvote 0
No problem. The Union is failing because it wants more than one range. If your columns are contiguous, as they are here, you can forego it. Also, you are typing in dates and times? If yes, then the code needs a slight tweak (see below).

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">If</SPAN> Target.Count <> 1 _
    <SPAN style="color:#00007F">Or</SPAN> Target.Row <= 2 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, [D:H]) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    <SPAN style="color:#00007F">If</SPAN> (Target - Int(Target)) < TimeSerial(8, 0, 0) <SPAN style="color:#00007F">Then</SPAN> Target = Target + 0.5

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>




</FONT>
 
Upvote 0
Great!! Thanks Greg.

Don't go too far. I am still testing this thing. I might need your assistance tomorrow if you are available.

Thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top