sorting question
 

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

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

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

"Brian" wrote
....
how can I make the data in the C cells blank
if there is no time in the A cell?

Try instead in C1:

=IF(A1="","",IF(A1=1540,0,IF(AND(A11540,A1<2400), A1-1540,A1+820)))

Copy down by a safe, max expected # of rows
to say, C100?
(can copy down ahead of input in col A)
--
And if you want an auto-sort on a new sheet,
try this play ..

(Assume the source data above is in Sheet1, cols A to C)

In a new sheet
--------------------
Put in A1:

=IF(ISERROR(MATCH(SMALL(Sheet1!$C:$C,ROW(A1)),Shee t1!$C:$C,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$C:$C,ROW(A1)),Sheet1! $C:$C,0)-1,COLUMN(A1)-1))

Copy across to B1, fill down by as many rows
as was done in col C in Sheet1, viz. down to B100

Format col A as Custom, Type: 0000

Cols A and B will return what's in cols A and B in Sheet1,
auto-sorted in ascending order from start of the shift at 1540
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Very Awesome,
Thank You Max

"Max" wrote:

"Brian" wrote
....
how can I make the data in the C cells blank
if there is no time in the A cell?

Try instead in C1:

=IF(A1="","",IF(A1=1540,0,IF(AND(A11540,A1<2400), A1-1540,A1+820)))

Copy down by a safe, max expected # of rows
to say, C100?
(can copy down ahead of input in col A)
--
And if you want an auto-sort on a new sheet,
try this play ..

(Assume the source data above is in Sheet1, cols A to C)

In a new sheet
--------------------
Put in A1:

=IF(ISERROR(MATCH(SMALL(Sheet1!$C:$C,ROW(A1)),Shee t1!$C:$C,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$C:$C,ROW(A1)),Sheet1! $C:$C,0)-1,COLUMN(A1)-1))

Copy across to B1, fill down by as many rows
as was done in col C in Sheet1, viz. down to B100

Format col A as Custom, Type: 0000

Cols A and B will return what's in cols A and B in Sheet1,
auto-sorted in ascending order from start of the shift at 1540
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

You're welcome, Brian !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Brian" wrote
Very Awesome,
Thank You Max