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