I have a worksheet where employees will be entering in times. I do
caculations based on these times and I would like a way so if the user enters
inthe time like "10:00pm" that excel will correct it to display "10:00 pm".
Is there a way to check for this?

Would something like this do what you want?:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

If IsNumeric(Target) Then Exit Sub

If Right(UCase(Target.Value), 2) = "AM" Then _
Target.Value = Left(Target.Value, Len(Target.Value) - 2) & " AM"

If Right(UCase(Target.Value), 2) = "PM" Then _
Target.Value = Left(Target.Value, Len(Target.Value) - 2) & " PM"
End Sub

It also works for just 10pm

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"Greg H." <(E-Mail Removed)> wrote in message
news:6D7928DC-3841-493B-B690-(E-Mail Removed)...
>I have a worksheet where employees will be entering in times. I do
> caculations based on these times and I would like a way so if the user
> enters
> inthe time like "10:00pm" that excel will correct it to display "10:00
> pm".
> Is there a way to check for this?
>

Better make that:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

If IsNumeric(Target) Then Exit Sub

On Error GoTo GetOut

Application.EnableEvents = False

If Right(UCase(Target.Value), 2) = "AM" Then _
Target.Value = Left(Target.Value, Len(Target.Value) - 2) & " AM"

If Right(UCase(Target.Value), 2) = "PM" Then _
Target.Value = Left(Target.Value, Len(Target.Value) - 2) & " PM"
GetOut:
Application.EnableEvents = True
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"Greg H." <(E-Mail Removed)> wrote in message
news:6D7928DC-3841-493B-B690-(E-Mail Removed)...
>I have a worksheet where employees will be entering in times. I do
> caculations based on these times and I would like a way so if the user
> enters
> inthe time like "10:00pm" that excel will correct it to display "10:00
> pm".
> Is there a way to check for this?
>

That is perfect. Thanks so much

"Sandy Mann" wrote:

> Better make that:
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
> If Target.Cells.Count > 1 Then Exit Sub
>
> If IsNumeric(Target) Then Exit Sub
>
> On Error GoTo GetOut
>
> Application.EnableEvents = False
>
> If Right(UCase(Target.Value), 2) = "AM" Then _
> Target.Value = Left(Target.Value, Len(Target.Value) - 2) & " AM"
>
> If Right(UCase(Target.Value), 2) = "PM" Then _
> Target.Value = Left(Target.Value, Len(Target.Value) - 2) & " PM"
> GetOut:
> Application.EnableEvents = True
> End Sub
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> (E-Mail Removed)
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Greg H." <(E-Mail Removed)> wrote in message
> news:6D7928DC-3841-493B-B690-(E-Mail Removed)...
> >I have a worksheet where employees will be entering in times. I do
> > caculations based on these times and I would like a way so if the user
> > enters
> > inthe time like "10:00pm" that excel will correct it to display "10:00
> > pm".
> > Is there a way to check for this?
> >
>
>
>

Glad that it worked for you, thanks for the feedback.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"Greg H." <(E-Mail Removed)> wrote in message
news:2A9A8A9D-4ABE-4A22-B5E2-(E-Mail Removed)...
> That is perfect. Thanks so much
>
> "Sandy Mann" wrote:
>
>> Better make that:
>>
>> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>> If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
>> If Target.Cells.Count > 1 Then Exit Sub
>>
>> If IsNumeric(Target) Then Exit Sub
>>
>> On Error GoTo GetOut
>>
>> Application.EnableEvents = False
>>
>> If Right(UCase(Target.Value), 2) = "AM" Then _
>> Target.Value = Left(Target.Value, Len(Target.Value) - 2) & "
>> AM"
>>
>> If Right(UCase(Target.Value), 2) = "PM" Then _
>> Target.Value = Left(Target.Value, Len(Target.Value) - 2) & "
>> PM"
>> GetOut:
>> Application.EnableEvents = True
>> End Sub
>>
>> --
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>> and the crowning place of kings
>>
>> (E-Mail Removed)
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "Greg H." <(E-Mail Removed)> wrote in message
>> news:6D7928DC-3841-493B-B690-(E-Mail Removed)...
>> >I have a worksheet where employees will be entering in times. I do
>> > caculations based on these times and I would like a way so if the user
>> > enters
>> > inthe time like "10:00pm" that excel will correct it to display "10:00
>> > pm".
>> > Is there a way to check for this?
>> >
>>
>>
>>
>

> If Right(UCase(Target.Value), 2) = "AM" Then _
> Target.Value = Left(Target.Value, Len(Target.Value) - 2) & "
> AM"
>
> If Right(UCase(Target.Value), 2) = "PM" Then _
> Target.Value = Left(Target.Value, Len(Target.Value) - 2) & "
> PM"

I believe you can replace the above code lines with this single line...

Target.Value = CDate(Target.Value)

and it will successfully handle 10:00p and 10:00a also. Of course, it
returns the seconds as well, but the cell can be Custom Formatted to handle
that; or we could just do it in code...

Target.Value = Replace(CDate(Target.Value), ":00 ", " ")

Rick

Also, in thinking about it, instead of this test...

> If IsNumeric(Target) Then Exit Sub

maybe this one would be more robust...

If Not IsDate(Target.Value) Then Exit Sub

Rick


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>> If Right(UCase(Target.Value), 2) = "AM" Then _
>> Target.Value = Left(Target.Value, Len(Target.Value) - 2) & "
>> AM"
>>
>> If Right(UCase(Target.Value), 2) = "PM" Then _
>> Target.Value = Left(Target.Value, Len(Target.Value) - 2) & "
>> PM"
>
> I believe you can replace the above code lines with this single line...
>
> Target.Value = CDate(Target.Value)
>
> and it will successfully handle 10:00p and 10:00a also. Of course, it
> returns the seconds as well, but the cell can be Custom Formatted to
> handle that; or we could just do it in code...
>
> Target.Value = Replace(CDate(Target.Value), ":00 ", " ")
>
> Rick

Very good Rick. My XL97 doesn't like Replace being used like that but
something to remember when I become posh. <g>

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> Also, in thinking about it, instead of this test...
>
>> If IsNumeric(Target) Then Exit Sub
>
> maybe this one would be more robust...
>
> If Not IsDate(Target.Value) Then Exit Sub
>
> Rick
>
>
> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
>>> If Right(UCase(Target.Value), 2) = "AM" Then _
>>> Target.Value = Left(Target.Value, Len(Target.Value) - 2) & "
>>> AM"
>>>
>>> If Right(UCase(Target.Value), 2) = "PM" Then _
>>> Target.Value = Left(Target.Value, Len(Target.Value) - 2) & "
>>> PM"
>>
>> I believe you can replace the above code lines with this single line...
>>
>> Target.Value = CDate(Target.Value)
>>
>> and it will successfully handle 10:00p and 10:00a also. Of course, it
>> returns the seconds as well, but the cell can be Custom Formatted to
>> handle that; or we could just do it in code...
>>
>> Target.Value = Replace(CDate(Target.Value), ":00 ", " ")
>>
>> Rick
>
>

But the CDate worked in XL97, right?

As for the Replace function, you might be able to use one of the VB coded
functions on this page to duplicate the Replace function's capabilities...

http://www.xbeat.net/vbspeed/c_Replace.htm

You can find other VB coded functions for the other newer String functions
under the VB6 to VB5 column on this webpage...

http://www.xbeat.net/vbspeed/

Rick


"Sandy Mann" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Very good Rick. My XL97 doesn't like Replace being used like that but
> something to remember when I become posh. <g>
>
> --
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> (E-Mail Removed)
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
>> Also, in thinking about it, instead of this test...
>>
>>> If IsNumeric(Target) Then Exit Sub
>>
>> maybe this one would be more robust...
>>
>> If Not IsDate(Target.Value) Then Exit Sub
>>
>> Rick
>>
>>
>> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote
>> in message news:(E-Mail Removed)...
>>>> If Right(UCase(Target.Value), 2) = "AM" Then _
>>>> Target.Value = Left(Target.Value, Len(Target.Value) - 2) & "
>>>> AM"
>>>>
>>>> If Right(UCase(Target.Value), 2) = "PM" Then _
>>>> Target.Value = Left(Target.Value, Len(Target.Value) - 2) & "
>>>> PM"
>>>
>>> I believe you can replace the above code lines with this single line...
>>>
>>> Target.Value = CDate(Target.Value)
>>>
>>> and it will successfully handle 10:00p and 10:00a also. Of course, it
>>> returns the seconds as well, but the cell can be Custom Formatted to
>>> handle that; or we could just do it in code...
>>>
>>> Target.Value = Replace(CDate(Target.Value), ":00 ", " ")
>>>
>>> Rick
>>
>>
>
>

Yes CDate worked fine.

Thanks for the links, I will check them out.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
> But the CDate worked in XL97, right?
>
> As for the Replace function, you might be able to use one of the VB coded
> functions on this page to duplicate the Replace function's capabilities...
>
> http://www.xbeat.net/vbspeed/c_Replace.htm
>
> You can find other VB coded functions for the other newer String functions
> under the VB6 to VB5 column on this webpage...
>
> http://www.xbeat.net/vbspeed/
>
> Rick
>
>
> "Sandy Mann" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Very good Rick. My XL97 doesn't like Replace being used like that but
>> something to remember when I become posh. <g>
>>
>> --
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>> and the crowning place of kings
>>
>> (E-Mail Removed)
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote
>> in message news:(E-Mail Removed)...
>>> Also, in thinking about it, instead of this test...
>>>
>>>> If IsNumeric(Target) Then Exit Sub
>>>
>>> maybe this one would be more robust...
>>>
>>> If Not IsDate(Target.Value) Then Exit Sub
>>>
>>> Rick
>>>
>>>
>>> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote
>>> in message news:(E-Mail Removed)...
>>>>> If Right(UCase(Target.Value), 2) = "AM" Then _
>>>>> Target.Value = Left(Target.Value, Len(Target.Value) - 2) &
>>>>> " AM"
>>>>>
>>>>> If Right(UCase(Target.Value), 2) = "PM" Then _
>>>>> Target.Value = Left(Target.Value, Len(Target.Value) - 2) &
>>>>> " PM"
>>>>
>>>> I believe you can replace the above code lines with this single line...
>>>>
>>>> Target.Value = CDate(Target.Value)
>>>>
>>>> and it will successfully handle 10:00p and 10:00a also. Of course, it
>>>> returns the seconds as well, but the cell can be Custom Formatted to
>>>> handle that; or we could just do it in code...
>>>>
>>>> Target.Value = Replace(CDate(Target.Value), ":00 ", " ")
>>>>
>>>> Rick
>>>
>>>
>>
>>
>
>