Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Formatting
I am trying to speed up the rate that people can enter information into a
spreadsheet. I don't know exactly how to describe this... so I'll give a few examples. When someone types in '8-4', Excel changes the field to '4 Aug'. Instead, I'd like it to read as '8:00 AM - 4:00 PM'. More examples below Entered Desired 8-4 8:00 AM - 4:00 PM 1-5 1:00 PM - 5:00 PM 7-3:30 7:00 AM - 3:30 PM I tried to format the cells to do this for me, but I can't figure it out. I could do this in Access, but Excel doesn't have any VB coding as far as I can tell. I know that accomplishing this is probably overly complicated, but if anyone has any suggestions, I would greatly appreciate it. Thanks, Nick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Formatting
Couple of issues:
1) XL certainly has "VB Coding" - XL macros are written in VBA. Event macros can be fired on cell entry. 2) XL's parser will parse the entry before any macros can read the data. The only way to enter 8-4 and NOT have it interpreted as a date is to make sure the cell is formatted as Text (so it is not parsed). 3) Before spending time writing an event macro, you don't give enough info to be definitive. Should 6-6 be 6:00 AM - 6:00 PM, or 6:00 PM to 6:00 AM? What about 5-7 or 7-5? What criteria should be used to determine AM/PM? In article , "Tatakau" wrote: I am trying to speed up the rate that people can enter information into a spreadsheet. I don't know exactly how to describe this... so I'll give a few examples. When someone types in '8-4', Excel changes the field to '4 Aug'. Instead, I'd like it to read as '8:00 AM - 4:00 PM'. More examples below Entered Desired 8-4 8:00 AM - 4:00 PM 1-5 1:00 PM - 5:00 PM 7-3:30 7:00 AM - 3:30 PM I tried to format the cells to do this for me, but I can't figure it out. I could do this in Access, but Excel doesn't have any VB coding as far as I can tell. I know that accomplishing this is probably overly complicated, but if anyone has any suggestions, I would greatly appreciate it. Thanks, Nick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Formatting
Nick,
Try this. It assumes that the dates are in column L (12), so modify to suit. Also, format that column as text beforehand Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iPos1 As Long Dim ipos2 As Long Dim sTemp1 As String Dim sTemp2 As String On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 12 Then iPos1 = InStr(.Value, "-") If iPos1 0 Then sTemp1 = Left(.Value, iPos1 - 1) If InStr(sTemp1, ":") = 0 Then sTemp1 = sTemp1 & ":0" End If sTemp2 = Right(.Value, Len(.Value) - iPos1) If InStr(sTemp2, ":") = 0 Then sTemp2 = sTemp2 & ":0" End If .Value = Format(TimeValue(sTemp1), "hh:mm AM/PM") & " - " & _ Format(TimeValue(sTemp2) + 0.5, "hh:mm AM/PM") Else sTemp1 = .Value If InStr(sTemp1, ":") = 0 Then sTemp1 = sTemp1 & ":0" End If .Value = Format(TimeValue(sTemp1), "hh:mm AM/PM") End If End If End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "Tatakau" wrote in message ... I am trying to speed up the rate that people can enter information into a spreadsheet. I don't know exactly how to describe this... so I'll give a few examples. When someone types in '8-4', Excel changes the field to '4 Aug'. Instead, I'd like it to read as '8:00 AM - 4:00 PM'. More examples below Entered Desired 8-4 8:00 AM - 4:00 PM 1-5 1:00 PM - 5:00 PM 7-3:30 7:00 AM - 3:30 PM I tried to format the cells to do this for me, but I can't figure it out. I could do this in Access, but Excel doesn't have any VB coding as far as I can tell. I know that accomplishing this is probably overly complicated, but if anyone has any suggestions, I would greatly appreciate it. Thanks, Nick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time Formatting
Excellent! I left out the "+ 0.5" - it's easier to just have users use the
24 hour system than to write a program to make a guess. But this is exactly what I was looking for! Thank you! Nick "Bob Phillips" wrote: Nick, Try this. It assumes that the dates are in column L (12), so modify to suit. Also, format that column as text beforehand Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iPos1 As Long Dim ipos2 As Long Dim sTemp1 As String Dim sTemp2 As String On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 12 Then iPos1 = InStr(.Value, "-") If iPos1 0 Then sTemp1 = Left(.Value, iPos1 - 1) If InStr(sTemp1, ":") = 0 Then sTemp1 = sTemp1 & ":0" End If sTemp2 = Right(.Value, Len(.Value) - iPos1) If InStr(sTemp2, ":") = 0 Then sTemp2 = sTemp2 & ":0" End If .Value = Format(TimeValue(sTemp1), "hh:mm AM/PM") & " - " & _ Format(TimeValue(sTemp2) + 0.5, "hh:mm AM/PM") Else sTemp1 = .Value If InStr(sTemp1, ":") = 0 Then sTemp1 = sTemp1 & ":0" End If .Value = Format(TimeValue(sTemp1), "hh:mm AM/PM") End If End If End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "Tatakau" wrote in message ... I am trying to speed up the rate that people can enter information into a spreadsheet. I don't know exactly how to describe this... so I'll give a few examples. When someone types in '8-4', Excel changes the field to '4 Aug'. Instead, I'd like it to read as '8:00 AM - 4:00 PM'. More examples below Entered Desired 8-4 8:00 AM - 4:00 PM 1-5 1:00 PM - 5:00 PM 7-3:30 7:00 AM - 3:30 PM I tried to format the cells to do this for me, but I can't figure it out. I could do this in Access, but Excel doesn't have any VB coding as far as I can tell. I know that accomplishing this is probably overly complicated, but if anyone has any suggestions, I would greatly appreciate it. Thanks, Nick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting time | Excel Discussion (Misc queries) | |||
Time Formatting | Excel Discussion (Misc queries) | |||
Time Formatting | Excel Discussion (Misc queries) | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |