Hi, We are trying to conduct a sort, however, the sort is not working.


This is what we are trying to sort:

Jan 09-12
Jan 16-19
Jan 23-26
May 22-24
Jul 10-12
Jul 31-Aug 7
Aug 28-30
Feb 22-Mar 2
(Col. A) (Col. B)

We made a custom sort with our fiscal year starting in Oct. And, for
some reason, Feb will not follow the sort.

Also, in Col. B, 2 cells are saying there is an error with the date
text format, and we made those fields text cells. And, i am sure those
2 cells are not causing the problem (hopefully).

Any suggestions would be quite helpful.

Try using the vlookup function to create a numeric value for you month codes:

Starting at cell A1 on a separate sheet (Sheet2 in this example) enter a
table that contains the month codes in column A and numbers from 1-12 in
column B:

Jan 1
Feb 2
Mar 3
Apr 4
May 5
etc...


Back on you sheet with the data, enter the following formula in column C
starting in row 2 assuming that is where you data starts:

=VLOOKUP(A2,Sheet2!$A$1:$B$12,2,FALSE)

Copy/paste the formula for the entire range of data.

Enter the following formula in column D starting again at row 2:

=VALUE(LEFT(B2,2))

Copy/paste the formula for the entire range of data.

This should give you two columns of numbers, C and D, that can be used to
sort the data.

Regards...


"spfas" wrote:

Hi, We are trying to conduct a sort, however, the sort is not working.


This is what we are trying to sort:

Jan 09-12
Jan 16-19
Jan 23-26
May 22-24
Jul 10-12
Jul 31-Aug 7
Aug 28-30
Feb 22-Mar 2
(Col. A) (Col. B)

We made a custom sort with our fiscal year starting in Oct. And, for
some reason, Feb will not follow the sort.

Also, in Col. B, 2 cells are saying there is an error with the date
text format, and we made those fields text cells. And, i am sure those
2 cells are not causing the problem (hopefully).

Any suggestions would be quite helpful.

Thank you. We will try that.


ChristopherTri wrote:
Try using the vlookup function to create a numeric value for you month codes:

Starting at cell A1 on a separate sheet (Sheet2 in this example) enter a
table that contains the month codes in column A and numbers from 1-12 in
column B:

Jan 1
Feb 2
Mar 3
Apr 4
May 5
etc...


Back on you sheet with the data, enter the following formula in column C
starting in row 2 assuming that is where you data starts:

=VLOOKUP(A2,Sheet2!$A$1:$B$12,2,FALSE)

Copy/paste the formula for the entire range of data.

Enter the following formula in column D starting again at row 2:

=VALUE(LEFT(B2,2))

Copy/paste the formula for the entire range of data.

This should give you two columns of numbers, C and D, that can be used to
sort the data.

Regards...


"spfas" wrote:

Hi, We are trying to conduct a sort, however, the sort is not working.


This is what we are trying to sort:

Jan 09-12
Jan 16-19
Jan 23-26
May 22-24
Jul 10-12
Jul 31-Aug 7
Aug 28-30
Feb 22-Mar 2
(Col. A) (Col. B)

We made a custom sort with our fiscal year starting in Oct. And, for
some reason, Feb will not follow the sort.

Also, in Col. B, 2 cells are saying there is an error with the date
text format, and we made those fields text cells. And, i am sure those
2 cells are not causing the problem (hopefully).

Any suggestions would be quite helpful.