Here's my problem:

I want to be able to input time as hhmma/p and get hh:mm AM/PM forma
back out. For example, if I need to enter the time 9:34 AM, I woul
key "0934a" and Excel would format the cell as "9:34 AM". The sam
thing for pm only "0934p" would be entered. I have a VBA macro tha
converts 24 time to the format I'm looking for, but it'd be a lo
easier for the people entering data to stick with 12 hour format. Ca
anyone please help me achieve this result? I know next to nothin
about VBA programming, but I'm GREAT at copy-paste! ;)

Thanks for any help you can provide

--
Message posted from http://www.ExcelForum.com

See if this page will help,

http://www.cpearson.com/excel/DateTimeEntry.htm

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

"sup191 " wrote in message
...
Here's my problem:

I want to be able to input time as hhmma/p and get hh:mm AM/PM format
back out. For example, if I need to enter the time 9:34 AM, I would
key "0934a" and Excel would format the cell as "9:34 AM". The same
thing for pm only "0934p" would be entered. I have a VBA macro that
converts 24 time to the format I'm looking for, but it'd be a lot
easier for the people entering data to stick with 12 hour format. Can
anyone please help me achieve this result? I know next to nothing
about VBA programming, but I'm GREAT at copy-paste! ;)

Thanks for any help you can provide!


---
Message posted from http://www.ExcelForum.com/

That the exact same code I'm using, but it doesn't check for the "a" o
"p' at the end of the time. It assumes everything will be in 24-hou
format

--
Message posted from http://www.ExcelForum.com

Try this amended version.It's even clever enough to translate 1300 tgo
01:00PM.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String
Dim fAMPM As Boolean

On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If UCase(Right(.Value, 1)) = "A" Then
.Value = Left(.Value, Len(.Value) - 1)
ElseIf UCase(Right(.Value, 1)) = "P" Then
.Value = Left(.Value, Len(.Value) - 1) + 1200
End If
.NumberFormat = "hh:mm AM/PM"
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub





--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"sup191 " wrote in message
...
That the exact same code I'm using, but it doesn't check for the "a" or
"p' at the end of the time. It assumes everything will be in 24-hour
format.


---
Message posted from http://www.ExcelForum.com/

Bob Phillips wrote:
[b]Try this amended version.It's even clever enough to translate 1300
tgo
01:00PM.


That did the trick!! Thank you so much! I haven't programmed since
the original, non-visual basic. A lot was starting to come back, but
I'd have never figured this one out. Now that this whole deal has
regained my interest, I'm going to start diggnig into VBA a little
more. :)

Thanks again Bob!


---
Message posted from http://www.ExcelForum.com/

"sup191 " wrote in message
...
Bob Phillips wrote:
[b]Try this amended version.It's even clever enough to translate 1300
tgo
01:00PM.


That did the trick!! Thank you so much! I haven't programmed since
the original, non-visual basic. A lot was starting to come back, but
I'd have never figured this one out. Now that this whole deal has
regained my interest, I'm going to start diggnig into VBA a little
more. :)

Thanks again Bob!


It's a pleasure, and if it has re-kindled the spark, even better. Good luck
with it.