Format Times in AM/PM

[BCS2]

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

[Greg Truby]

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.

Private Sub Worksheet_Change(ByVal Target As Range)
****If Target.Count <> 1 _
****Or Target.Column <> 1 Then Exit Sub
****If Target < TimeSerial(8, 0, 0) Then Target = Target + 0.5
End Sub


[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]

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]

Originally Posted by Greg Truby

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

[Greg Truby]

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:

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

Regards,

[BCS2]

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]

On routine, but for better clarity in reading the code, probably better to split the IF into two pieces:

Private Sub Worksheet_Change(ByVal Target As Range)
****If Target.Count <> 1 _
****Or Target.Row <= 2 Then Exit Sub
****
****If Intersect(Target, Union([B:C], [F:F], [I:M])) Is Nothing Then Exit Sub
****
****If Target < TimeSerial(8, 0, 0) Then Target = Target + 0.5

End Sub



The above would only execute on columns B, C, F, I, J, K, L, and M.

[BCS2]

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.

******** ******************** ************************************************************************>

Microsoft Excel - Estimate tracking.xls ___Running: xl2000 : OS = Windows 98
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
D2 =
	D	E	F	G	H
2	E-mail*date*/*time	Print*out*date*/*time	Due*date*/*time	Start*date*/*time	Date*/*Time*delivered
3	1/6/04*9:42*AM	1/6/04*9:45*AM	1/6/04*5:00*PM	*	1/6/04*10:49*AM
4	1/6/04*9:41*AM	1/6/04*9:45*AM	1/6/04*5:00*PM	*	1/6/04*12:00*PM
5	1/6/04*1:38*PM	1/6/04*1:49*AM	1/7/04*12:00*PM	*	1/6/04*2:40*PM
Estimate Tracking *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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]

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).

Private Sub Worksheet_Change(ByVal Target As Range)
****If Target.Count <> 1 _
****Or Target.Row <= 2 Then Exit Sub
****
****If Intersect(Target, [D:H]) Is Nothing Then Exit Sub
****
****If (Target - Int(Target)) < TimeSerial(8, 0, 0) Then Target = Target + 0.5

End Sub

[BCS2]

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.