Split "9:00 - 5:30pm" to "9:00" "17:30"
 

The times in a roster are shown in the following way:

9:00 - 5:30pm



I need to copy these times to a timesheet and split the start and finish
times into separate cells.



Like this:



If Worksheet("Sheet1").Range("A1") .value = "9:00 - 5:30pm" then

Worksheet("Sheet2").Range("A1") .value = "9:00"

Worksheet("Sheet2").Range("B1") .value = "17:30"

End if



But the start and finish times can be anything. They may be "10:00 - 8:30pm"
etc etc.

Split "9:00 - 5:30pm" to "9:00" "17:30"
 

Lucas,

Sub TryNow()
Dim myStr As String
Dim myCell As Range

On Error Resume Next
For Each myCell In Worksheets("Sheet1").Range("A1:A10")

myStr = myCell.Value
If myStr < "" Then
Worksheets("Sheet2").Range("A" & myCell.Row).Value = _
TimeValue(Left(myStr, _
InStr(1, myStr, " ") - 1))
Worksheets("Sheet2").Range("B" & myCell.Row).Value = _
TimeValue(Mid(myStr, _
InStr(1, myStr, " - ") + 3, 200))
End If
Next myCell

End Sub


HTH,
Bernie
MS Excel MVP


"Lucas Budlong" wrote in message
...
The times in a roster are shown in the following way:

9:00 - 5:30pm



I need to copy these times to a timesheet and split the start and finish times into separate
cells.



Like this:



If Worksheet("Sheet1").Range("A1") .value = "9:00 - 5:30pm" then

Worksheet("Sheet2").Range("A1") .value = "9:00"

Worksheet("Sheet2").Range("B1") .value = "17:30"

End if



But the start and finish times can be anything. They may be "10:00 - 8:30pm" etc etc.

Split "9:00 - 5:30pm" to "9:00" "17:30"
 

Have you tried doing a text to columns. Set the "-" as the delimiter and it
will split the text into two times. The times can be formatted however you
like using standard Excel formatting...
--
HTH...

Jim Thomlinson


"Lucas Budlong" wrote:

The times in a roster are shown in the following way:

9:00 - 5:30pm



I need to copy these times to a timesheet and split the start and finish
times into separate cells.



Like this:



If Worksheet("Sheet1").Range("A1") .value = "9:00 - 5:30pm" then

Worksheet("Sheet2").Range("A1") .value = "9:00"

Worksheet("Sheet2").Range("B1") .value = "17:30"

End if



But the start and finish times can be anything. They may be "10:00 - 8:30pm"
etc etc.

Split "9:00 - 5:30pm" to "9:00" "17:30"
 

Thanks Bernie,
That's perfect!!!
L.

Bernie Deitrick wrote:
Lucas,

Sub TryNow()
Dim myStr As String
Dim myCell As Range

On Error Resume Next
For Each myCell In Worksheets("Sheet1").Range("A1:A10")

myStr = myCell.Value
If myStr < "" Then
Worksheets("Sheet2").Range("A" & myCell.Row).Value = _
TimeValue(Left(myStr, _
InStr(1, myStr, " ") - 1))
Worksheets("Sheet2").Range("B" & myCell.Row).Value = _
TimeValue(Mid(myStr, _
InStr(1, myStr, " - ") + 3, 200))
End If
Next myCell

End Sub


HTH,
Bernie
MS Excel MVP


"Lucas Budlong" wrote in message
...
The times in a roster are shown in the following way:

9:00 - 5:30pm



I need to copy these times to a timesheet and split the start and finish times into separate
cells.



Like this:



If Worksheet("Sheet1").Range("A1") .value = "9:00 - 5:30pm" then

Worksheet("Sheet2").Range("A1") .value = "9:00"

Worksheet("Sheet2").Range("B1") .value = "17:30"

End if



But the start and finish times can be anything. They may be "10:00 - 8:30pm" etc etc.