Sorting times - getting messy
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****** |
All times are GMT +1. The time now is 07:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com