Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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****** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Messy Formula needs clean up help! | Excel Worksheet Functions | |||
VBA not working - Messy. | Excel Worksheet Functions | |||
Messy tab characters | Excel Discussion (Misc queries) | |||
times and sorting/category on all < 30 mins | New Users to Excel | |||
sorting multiple minimum times | Excel Discussion (Misc queries) |