Sorting times - getting messy

M

Martin

Using Win98 and Excel2000, I have a spreadsheet where times
have been imported from a website.
If the imported time is "out", the time I want to show
is the equivalent of system time less 20 minutes but the
times imported can be:

accurate,
up by 12 hours,
down by 12 hours or
"No Time".

The following code works, but this is for one series,
I have four others to do in a similar manner and
it seems like a long way round.
Is there a better approach? Any advice?

Thank you

Martin

'******StartCode******
'Times to check - Cells(rowndx, colnum)
'System Time - Cells(rowndx, colnum).Offset(0, 8)
'Desired Time - Cells(rowndx, colnum).Offset(0, 6)

colnum = Selection(1).Column
For rowndx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row Step -1

'If "No Time", then system time less 20 minutes

If IsDate(Cells(rowndx, colnum).Text) = False Then
Cells( _
rowndx, colnum).Offset( _
0, 6).Value = Cells( _
rowndx, colnum).Offset( _
0, 8).Value - TimeValue("00:20:00")
End If
'If there is a time and it is after 07:25 and before 17:00
'then take imported time

If IsDate(Cells(rowndx, colnum).Text) = True Then
If Cells(rowndx, colnum).Value > 0.3090278 Then
If Cells(rowndx, colnum).Value < 0.7083333 Then
Cells( _
rowndx, colnum).Offset(0, 6).Value = Cells( _
rowndx, colnum).Value

'otherwise, take system time + 12 hours

Else: Cells( _
rowndx, colnum).Offset(0, 6).Value = Cells( _
rowndx, colnum).Value + 0.5
End If
End If
End If

'If "+12 hours" above takes you into the next day, take away 1

If Cells( _
rowndx, colnum).Offset(0, 6).Value > 1 Then
Cells( _
rowndx, colnum).Offset( _
0, 6).Value = Cells( _
rowndx, colnum).Offset( _
0, 6).Value - 1

Next rowndx

'******EndCode******
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top