time conversion
 

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

time conversion
 

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/

time conversion
 

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

time conversion
 

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/

time conversion
 

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/

time conversion
 

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

time conversion
 

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?


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

time conversion
 

Try this

--

HTH

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

"sup191 " wrote in message
...
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?


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

time conversion
 

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" wrote in message
...

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

time conversion
 

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

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

time conversion
 

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)

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


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

time conversion
 

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

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

time conversion
 

"sup191 " wrote in message
...
Thanks for coming through yet AGAIN for me! I think I owe you at least
a six pack by now... :)


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.

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


Look forward to it.

time conversion
 

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

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