|
Correcting time input by users
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? |
Correcting time input by users
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 Replace @mailinator.com with @tiscali.co.uk "Greg H." wrote in message ... 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? |
Correcting time input by users
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 Replace @mailinator.com with @tiscali.co.uk "Greg H." wrote in message ... 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? |
Correcting time input by users
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 Replace @mailinator.com with @tiscali.co.uk "Greg H." wrote in message ... 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? |
Correcting time input by users
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 |
Correcting time input by users
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)" wrote in message ... 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 |
Correcting time input by users
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 Replace @mailinator.com with @tiscali.co.uk "Rick Rothstein (MVP - VB)" wrote in message ... 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)" wrote in message ... 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 |
Correcting time input by users
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" wrote in message ... 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 Replace @mailinator.com with @tiscali.co.uk "Rick Rothstein (MVP - VB)" wrote in message ... 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)" wrote in message ... 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 |
Correcting time input by users
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 Replace @mailinator.com with @tiscali.co.uk "Rick Rothstein (MVP - VB)" wrote in message ... 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" wrote in message ... 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 Replace @mailinator.com with @tiscali.co.uk "Rick Rothstein (MVP - VB)" wrote in message ... 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)" wrote in message ... 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 |
| All times are GMT +1. The time now is 06:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com