Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Conversion | Excel Worksheet Functions | |||
Time Conversion | Excel Discussion (Misc queries) | |||
Time conversion | Excel Worksheet Functions | |||
Time Conversion | Excel Discussion (Misc queries) | |||
time conversion | Excel Discussion (Misc queries) |