One way ..
Try sorting on a helper col?
Assuming the mil. times are in col A=20
(Numbers formatted as Custom: Type: 0000),
comments in col B,=20
re-sample data in A1:B7 below=20
1540 Start of shift
1700 ;jlakja;lkj;lkj
1600 lkjhlkjhlkjhlkjhljkh
0130 jkh;lkj;lkj;lkj;lkj;l
0200 ;lkasdjf;lkjsdf
2359 hjhj
0330 tyyuu
(Note: 12 midnight would be 0000 in col A)
Put in C1:
=3DIF(A1=3D1540,0,IF(AND(A11540,A1<2400),A1-1540,A1+820))
Copy down
Now sort A1:C7 by col C Ascending
You'll get the desired results in cols A and B:
1540 Start of shift
1600 lkjhlkjhlkjhlkjhljkh
1700 ;jlakja;lkj;lkj
2359 hjhj
0130 jkh;lkj;lkj;lkj;lkj;l
0200 ;lkasdjf;lkjsdf
0330 tyyuu
--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----
"Brian" wrote:
in column A from 31 down to 51 military time will be=20
entered along with a=20
corresponding note in column B also from 31 down to 51.
These times and notes are used to document occurences=20
during a work shift.
and notes often entered out of order. I want to sort the=20
times at the end of=20
a work shiftso that they will be placed in order along=20
with notes to the=20
right. =20
PROBLEM: There must be two separate worksheets for two=20
different shifts.
the first shift starts at 0340 and ends at 1540 no=20
problem when sorting=20
these times from the beggining of shift to its end,=20
HOWEVER, the second shift=20
which starts at 1540 and ends at 0340 presents a problem.=20
For instance assume=20
the first note of the shift is the following =20
1540 "Start of shift=20
all present"=20
=20
1700 ;jlakja;lkj;lkj
1600 =20
lkjhlkjhlkjhlkjhljkh
0130 =20
jkh;lkj;lkj;lkj;lkj;l
=20
0200 ;lkasdjf;lkjsdf
When I sort using a Button with assigned macro, 1700 and=20
1600 are inverted=20
which is correct, However 0130 and 0200 are placed before=20
1540. I understand=20
the the program recognized that logically 0130 and 0200=20
come before 1540, but=20
for the purposes of my worksheet, I need 1540 to be=20
recognized as the start=20
of time. Any solution would be greatly appreciated
Thank you=20
Brian
|