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

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

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

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