Sorting Data by date problem

  • Thread starter Thread starter Kevin Smith
  • Start date Start date
K

Kevin Smith

Dear All,

I have a bit of a problem sorting data but I feel it should be simple,
but I cannot find a way to do it :-)

What i want to achieve is to sort rows of data by date but insert
blank rows into the data at various points. These points would be if
the data is more than 15 days old and more than 5 days old. Therefore
it would look like this

Dates over 15 Days

(list of dates in order of more than 15 days ago but not less than 5
days ago)

Dates over 5 Days

(list of dates over 5 days old)

Dates OK

(Dates up to 5 days old)

Some of the dates used are Future dates and would be in the bottom
section as well. And I would like to insert the headings listed above
in between the data.

I am using Excel 97 to do this.

Any help would be greatly appreciated as I have torn hair out over
this.

Many Thanks

Kevin
 
You can do this by adding a column to your table, and using the Subtotal
command:

1. Insert a column in the table, and add a heading, e.g. Status
2. In the first row of data, enter a formula in the Status column
to calculate the record's date group. For example, if dates
are in column A:
=IF(TODAY()-A2>15,"Over 15 Days",IF(TODAY()-A2>5,"Over 5
Days",IF(TODAY()-A2>=0,"OK","Future Dates")))

3. Copy the formula down to the last row of data
4. Sort the table by Date
5. Choose Data>Subtotals
6. Choose 'At each change in' -- Status
7. Select a function, e.g. Sum or Count, and select a field to subtotal
8. Remove the check mark from 'Summary below data'
9. Click OK
 
Many many thanks for this!

I feel a little dumb for not working that out!

Thanks again

Kevin
 
Back
Top