changing date in "sort" to month order instead of

[ramsay]

HELP! Can someone tell me how to change the date in "sort" please? I have been keeping a table fo my invoices and should have been keeping them in "date paid" order... Now when I click on sort, it sorts it in day order i.e. 01 .02.04..... 02 being February. so instead of 01 being January it is the day.. does this make sense. I'm in a panic as my accountant now wants it this way! help???

 

[Norie]

Are you actually entering dates in that format?

If you are Excel is probably recognising them as text rather than a date and is sorting them as such.

You need to convert these 'dates' to real dates. Take a look at the DATE.

=DATE(RIGHT(A1,2), MID(A1,3,2), LEFT(A1,2))

 

[2rrs]

You might try adding a helper col with:

=MONTH(D3)*100+DAY(D3) (use to sort by month day date)

 

[ramsay]

Hi , Many thanks for both replies.

I am new to this and don't quite understand what to do and how to do it as you both explained. Can I have an "idiot's guide" please?


Would need step by step, really sorry to bother you.

Thanks

Jennifer

 

[Zack Barresse]

Hello, and welcome to the board!

Hmm, Excel will sort dates by their Serial Number, not the format in which it is shown on the spreadsheet. Give us some examples of what is in the formula bar of some of these cells. Because they are dates, they can be formatted to show about anything you want. A shortcut to show them in Serial Number format is to select them all and then press Ctrl + Shift + 3 (number format).

And it's definitely no bother!

 

[ramsay]

Hi Zack,

Many, many thanks for your reply.
The way I have the dates are;

e.g.

06.04.05
03.04.05
01.03.04
12.05.04
when I ask it to "sort" it comes out;
01.03.04
03.04.05
06.04.05
12.05.04 the first digits ( days) are in numerical order but NOT the months!
Does this make sense?
I will try your suggestion of ctrl +shift+3. here goes!
Regards
Jennifer

 

[Dutchy]

If they sort like that looks like text,

You probably need to use another column to convert, use Norie's suggestion or

=--SUBSTITUTE(A1,".","/")

If you put this in row 1 of a blank column and copy down the whole column you can then

Use Copy and Edit >Paste Special >Values

to paste over your original dates

edit: in fact Norie, shouldn't yours be

=DATE(RIGHT(A1,2), MID(A1,4,2), LEFT(A1,2))

 

[ramsay]

sorry to be so thick!
Just tried ctrl +shift+ 3. nothing happened!
I take it "shift" is the arrow above ctrl?

I did "select all" first! then held them all down at the same time. Is that right?

Regards
Jennifer

 

[Zack Barresse]

sorry to be so thick!
Just tried ctrl +shift+ 3. nothing happened!
I take it "shift" is the arrow above ctrl?

I did "select all" first! then held them all down at the same time. Is that right?

Regards
Jennifer

You must have missed Dutchy's post. You do not have an Excel recognized Date (to test: http://www.vbaexpress.com/kb/getarticle.php?kb_id=323 ). Make it an Excel recognized date and it will sort by date.

Dates are great for formatting because you can get them to look almost any way you want to. I suggest you play around with the formatting of them.

d m y
dd mm yy
dd mm yyyy
ddd mmm yyyy
dddd mmmm yyyy
(or any combination)