# Sorting results of using DATEDIFF

This is a discussion on Sorting results of using DATEDIFF within the Excel Questions forums, part of the Question Forums category; I have a list of dates i.e. 15/07/2003 where the format is custom dd/mm/yyyy. I have another column next to ...

1. ## Sorting results of using DATEDIFF

I have a list of dates i.e. 15/07/2003 where the format is custom dd/mm/yyyy. I have another column next to this date column which works out how many days there are between that date and today. The formula I am using is
=DATEDIF(F7,TODAY(),"d")&" day(s)"

where F7 is the cell in the date column and in this case for the example purpose is 15/07/2003. The result I get from the formula is 73 day(s).

I apply this formula for all the values which is about 900 rows.
I end up with a list from the formula ranging from 73 day(s) down to 1 day(s). I then have a filter on this column and if you do a custom filter to show all rows where the formula column is less than or equal to 4 day(s) I end up getting things like 32 days and 33 days etc etc as it thinks 32 is less than 4 because it is not sorting them correctly.

Any suggestions of how to solve this so that when I choose custom filter to see everything less than or equal to 4 days I only get rows showing 0-4 day(s).

If you need my problem emailing to you in a spreadsheet please let me know. Cheers
Jon

2. ## Re: Sorting results of using DATEDIFF

It might be easier to do the following-

1) Use this formula- =DATEDIF(F7,TODAY(),"d")
2) Custom format the cells as # "days"

This should then return a number which can be autofiltered properly as you would just enter numbers in the custom criteria (although strangely, a sort doesn't seem to work, anybody else?).

3. ## Re: Sorting results of using DATEDIFF

But then instead of 73 days I get 13 days....(it ignores the two months (i.e. 60 days))

It's a difficult one isn't it..... hopefully some solution will arise from somewhere....

Thanks

Jon

4. ## Re: Sorting results of using DATEDIFF

Originally Posted by Mudface
It might be easier to do the following-

1) Use this formula- =DATEDIF(F7,TODAY(),"d")
2) Custom format the cells as # "days"

This should then return a number which can be autofiltered properly as you would just enter numbers in the custom criteria (although strangely, a sort doesn't seem to work, anybody else?).
Chris,

Compute # of days, sort the data on # of days, then AutoFilter.

Am I missing something?

5. Works fine for me, Autofilter works with or without sorting first.

Peter

6. ## Re: Sorting results of using DATEDIFF

My fault I wasn't using # "days" as custom formatting data I was doing d "days".
So if I do my # "days" I do in fact get 73 days which is right.
If I then filter on 73 days I get all the 73 days rows which is right but if I do a custom filter on <= 4 days I get nothing returned. And yes there are rows there less than or equal to 4 days.

Hmmmm still stuck

Jon

7. ## Re: Sorting results of using DATEDIFF

If I do not bother to data format (custom) the cell so it shows 73 instead of 73 days then it works no problem.

Suppose solution is to do this and keep the cells as numbers
73
72

73 days
72 days

etc etc.

The person who is going to use my report will know the column is days as it will say so in the title !

If you do manage to get it to work when the cells are showing # "days" please drop me a reply - cheers for all help involved.

Jon

8. ## Re: Sorting results of using DATEDIFF

Originally Posted by Mudface
It might be easier to do the following-

1) Use this formula- =DATEDIF(F7,TODAY(),"d")
2) Custom format the cells as # "days"

This should then return a number which can be autofiltered properly as you would just enter numbers in the custom criteria (although strangely, a sort doesn't seem to work, anybody else?).
Chris,

Compute # of days, sort the data on # of days, then AutoFilter.

Am I missing something?
Sorry, must have been me, I couldn't get the column to sort properly initially, but redoing it, it seems fine . The autofilter also works with no problem with the criteria of less than or equal to 4, so I don't understand what the trouble is there.

Edit:- Ah, I think I see the problem- baylisj- when you use the custom autofilter, just use a figure not a figure followed by 'days' (ie '4' rather than 4 days). The days is only there as part of a format and isn't actually present in the cell.

9. ## Re: Sorting results of using DATEDIFF

Jon,

E2:
=B2<=4

List range: \$A\$1:\$B\$15
Criteria range: \$E\$1:\$E2
Copy to: \$G\$1:\$H\$1

(ensure that you click the radio button “Copy to another range”).

The data in A1:B15 does notneed to be sorted.

Regards,

Mike

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 B2E2B3B4B5B6B7B8B9B10B11B12B13B14B15 =

A
B
C
D
E
F
G
H
1
DateDays****DateDays
2
3-Aug-0354*days**FALSE*25-Sep-031*days
3
2-Aug-0355*days****24-Sep-032*days
4
25-Sep-031*days****22-Sep-034*days
5
25-May-03124*days****23-Sep-033*days
6
21-Sep-035*days****24-Sep-032*days
7
18-Apr-03161*days****24-Sep-032*days
8
22-Apr-03157*days******
9
24-Sep-032*days******
10
22-Sep-034*days******
11
23-Sep-033*days******
12
15-Aug-0342*days******
13
24-Sep-032*days******
14
19-Sep-037*days******
15
24-Sep-032*days******
16
********
 Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

10. ## Re: Sorting results of using DATEDIFF

Edit:- Ah, I think I see the problem- baylisj- when you use the custom autofilter, just use a figure not a figure followed by 'days' (ie '4' rather than 4 days). The days is only there as part of a format and isn't actually present in the cell.[/quote]
.......

Thanks mate !

Jon

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•