time conversion

S

sup191

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
 
P

Paul B

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 **
 
S

sup191

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
 
B

Bob Phillips

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

sup191

Bob said:
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!
Click to expand...
 
B

Bob Phillips

sup191 > said:
Bob said:
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!
Click to expand...
Click to expand...


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

sup191

I just noticed with the modified code above, any time between 1200p and
1259p gives an error and doesn't work. All other times seem to work
with no problems. Why?
 
B

Bob Phillips

Try this

--

HTH

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

Bob Phillips

Try this version

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
If .Value <= 1200 Then
.Value = Left(.Value, Len(.Value) - 1) + 1200
Else
.Value = Left(.Value, Len(.Value) - 1)
End If
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)

Bob Phillips said:
sup191 > said:
Bob said:
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!
Click to expand...
Click to expand...


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

sup191

I see what you're trying to do, but it's still giving some weir
results. Anything entered 100p to 1159p is coming out as the correc
hour & minute, but only in AM, not PM. Also 1200a to 1250a comes ou
12:XX PM
 
B

Bob Phillips

Sorry mate, a basic error on the first point. On the second, I worked on the
basis that 1201a is 00:01PM, but I have changed it.

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
If Left(.Value, Len(.Value) - 1) >= 1200 Then
.Value = Left(.Value, Len(.Value) - 1) - 1200
Else
.Value = Left(.Value, Len(.Value) - 1)
End If
ElseIf UCase(Right(.Value, 1)) = "P" Then
If Left(.Value, Len(.Value) - 1) <= 1200 Then
.Value = Left(.Value, Len(.Value) - 1) + 1200
Else
.Value = Left(.Value, Len(.Value) - 1)
End If
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)
 
S

sup191

Thanks for coming through yet AGAIN for me! I think I owe you at leas
a six pack by now... :)

One thing I changed was the elseif for the PM code to:

ElseIf UCase(Right(.Value, 1)) = "P" Then
If Left(.Value, Len(.Value) - 1) <= 1159 Then

1159 because <= 1200 would produce 2400 and my code didn't like it.
I'm slowly getting this!! Thanks again Bob and I'm sure I'll b
bugging you and more people on this group in the coming weeks. ;
 
B

Bob Phillips

sup191 > said:
Thanks for coming through yet AGAIN for me! I think I owe you at least
a six pack by now... :)
Click to expand...

I'll toast to you my man.
One thing I changed was the elseif for the PM code to:

ElseIf UCase(Right(.Value, 1)) = "P" Then
If Left(.Value, Len(.Value) - 1) <= 1159 Then

1159 because <= 1200 would produce 2400 and my code didn't like it.
Click to expand...

Excellent, that's what it's all about.
I'm slowly getting this!! Thanks again Bob and I'm sure I'll be
bugging you and more people on this group in the coming weeks. ;)
Click to expand...

Look forward to it.
 
G

GSTAFFORD

Great set of posts guys, helped me out a great deal. I was trying to se
up a timesheet of sorts that would use this macro for in and out time
but ran into a problem with break/lunch time deducting from a total fo
those hours. Anyone game for helping me tackle this
 
Top