Thanks Max,
One more question,
how can I make the data in the C cells blank if there is no time in the A
cell?
Brian
"Max" wrote:
One way ..
Try sorting on a helper col?
Assuming the mil. times are in col A
(Numbers formatted as Custom: Type: 0000),
comments in col B,
re-sample data in A1:B7 below
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:
=IF(A1=1540,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° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Brian" wrote:
in column A from 31 down to 51 military time will be
entered along with a
corresponding note in column B also from 31 down to 51.
These times and notes are used to document occurences
during a work shift.
and notes often entered out of order. I want to sort the
times at the end of
a work shiftso that they will be placed in order along
with notes to the
right.
PROBLEM: There must be two separate worksheets for two
different shifts.
the first shift starts at 0340 and ends at 1540 no
problem when sorting
these times from the beggining of shift to its end,
HOWEVER, the second shift
which starts at 1540 and ends at 0340 presents a problem.
For instance assume
the first note of the shift is the following
1540 "Start of shift
all present"
1700 ;jlakja;lkj;lkj
1600
lkjhlkjhlkjhlkjhljkh
0130
jkh;lkj;lkj;lkj;lkj;l
0200 ;lkasdjf;lkjsdf
When I sort using a Button with assigned macro, 1700 and
1600 are inverted
which is correct, However 0130 and 0200 are placed before
1540. I understand
the the program recognized that logically 0130 and 0200
come before 1540, but
for the purposes of my worksheet, I need 1540 to be
recognized as the start
of time. Any solution would be greatly appreciated
Thank you
Brian
|