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:
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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]
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
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
 

BCS2

Board Regular
Joined
Dec 8, 2003
Messages
67
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:
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

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,
 

BCS2

Board Regular
Joined
Dec 8, 2003
Messages
67
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?
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

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.
 

BCS2

Board Regular
Joined
Dec 8, 2003
Messages
67
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.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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>
 

BCS2

Board Regular
Joined
Dec 8, 2003
Messages
67
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

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
Top