how to prevent date format change

I am having problems with excel when I am input dates.

For example, if I input 17/01/11 into a cell, sometimes it appears as 01/17/11 or 17 01 11 or ###########

1) Is there a way so that what ever I input will be the same forever? For example if I input 17/01/11 is will appear as 17/01/11. If I input 17 Jan 11, it will appear as 17 Jan 11. I want to prevent excel from auto changing to other formats

2) I want to search the cell that contains the word Jan (or other months). So after searching I want to highlight the cell (or maybe if possible highlight the row) so that I can easily identify the rows with Jan. How can I do it?

3) Is there a way to sort rows by date? In my sheet, every row has a column that contains a date and there is another column that has a number for each row,
Somtimes I want to sort by date Jan on top most and Dec bottom most and sometimes I want to sort the row with the number 1 on top most and row with number 100 at the bottom most.
(Please advise me what is the best format to to enter dates as if I want to sort it frequently, I think format like 17/01/11 is more confusing as the date and month might be messed up. 17 Jan 11 looks better)

Thanks a lot.

Just to add more info

My data sample will be.

columnA columnB columnC columnD columnE
1 apple red ripe 17 Jan 11
2 orange orange rotten 22 Aug 10
3 papaya orange bad 02 Mar 08
4 lime green good 20 Jan 07
5 grapes green good 23 Jan 11


Sometimes I would like to see rows arranged by column A from smallest number on top to biggest number at bottom (as seen in sample data above)

Sometimes I would like to arrange by column E by the months, so I would like to sort all the Jan rows to the top.If possible, I would like to sort the years and date too, with most smaller number years and smaller dates to the top.

Sample result if I sort for the term Jan

columnA columnB columnC columnD columnE
4 lime green good 20 Jan 07
1 apple red ripe 17 Jan 11
5 grapes green good 23 Jan 11
2 orange orange rotten 22 Aug 10
3 papaya orange bad 02 Mar 08

Originally Posted by Marcol

Have you tried formatting Column E

Format > Format Cells Number > Custom "dd mmm yy" (without the quotes)

Hope this helps

I tried it and most of the rows do appear as 17 Jan 11 format, some of the rows still remains as 25/01/2011 format.

You solution in the previous reply works great. Exactly what I wanted. Thanks a lot!

One more question... how can I arrange the months that I want to see to the top?

What months to the top of where....

You would do best to post a sample workbook showing your Sheet Layout and perhaps Before and After examples.

It should clearly illustrate your problem and not contain any sensitive data.

A sample and the result I wanted in posted in my post #2 as seen in this thread.

Basically my worksheet has different rows with different dates in different rows, if I want to see the month Jan, I would like all the rows that contain the word Jan to be sorted to the top, so I can see what are the items that are going to due in Jan easily instead of scanning through the whole worksheet looking for rows that contain the word Jan in columnE

Thanks.

Originally Posted by martindwilson

try another column say F in f2 put

=TEXT(E2,"mmm")&"-"&YEAR(E2)
then use auto filter to display say feb-2011

how do you add the box E1 to have have the autofilter / dropdown menu?

from your auto filter dates.xls I can see the dropdown box for E1 has Jan-2011, Feb-2011, Mar-2011 to select (tick) from, how can I see the other months?

Originally Posted by Marcol

Slightly different from martin

In F2 (helper column)

Please Login or Register  to view this content.

Drag/Fill Down

Sort A:F (Two conditions)
By:= Helper Column > Ascending
Then by:= Date > Ascending

Then apply filter to Helper Column:= "Jan"

Hope this helps

Tip:=Posting a sample workbook, rather than some text in your post, is always an advantage.

I understand that you fill in F2 with =TEXT(E3,"mmm") then drag/fill down.

How do you do the following steps?
Sort A:F (Two conditions)
By:= Helper Column > Ascending
Then by:= Date > Ascending

Then apply filter to Helper Column:= "Jan"


In the F1 cell, I can select either Aug, Jan, Mar, how can I add more months?


How do you make the F1 cell with the drop down menu?


Thanks for your patience. I am a beginner, so some things that you mentioned I may not know what are the steps to do to achieve it.

Originally Posted by Marcol

See if following the steps in this pdf helps.

Your pdf is clear. I can understand now. Thanks for the explanation

As I followed along the pdf, after I clicked sort, in the "Sort by" dropdown box, it shows Column A, Column B, Column C, instead of the titles of the first row like helper Column, Date. Why is it so?

I noticed there are 2 ways to reach the sort button, is there a difference between the sort in
1. Home tab>Sort&Filter
2. Data tab>Sort

Originally Posted by Marcol

1/. I only put one header ("Helper Column") in the example in row1, as Excel can only read what is there it will identify other columns as Column A, B, etc... Fill in the rest of the headers and Excel will read them.

I do not get it.

If I start a new blank sheet, filled in A1 with id, B1 with fruit, C1 with color, D1 with condition, E1 with date, F1 with helper column. I selected the whole sheet. Then I go to Data>Sort. In the dropdown box of "Sort by", I would see Column A, Column B, Column C, Column D...

I do not see the headers name.

This is what I mean in thread #17. Refer to attached pic

I understand what you mean now.

Just one more question. Is it possible to have my headers on row 2? And let the headers appear in the "Sort by" after I checked the check box "My Data Has Headers"

I am using Row 1 for a title and a brief description of the sheet, so my headers will be on row 2.

Thanks

Why don't you just try it and see?
You can sort/filter any range you choose to select.

I'm sorry, but short of coming to Singapore and doing it for you, I can't see how I can help you any more.

Originally Posted by Marcol

Why don't you just try it and see?
You can sort/filter any range you choose to select.

I'm sorry, but short of coming to Singapore and doing it for you, I can't see how I can help you any more.

I think just forget about the last question. Its ok. I find excel always use the first row as the header. I did not figure out how to use the 2nd row as the header. I guess I will just delete the first row, so that the header description can occupy the first row, and the "sort by" will display the headers description.

Thanks for your time. I appreciate it.

This is what I have noticed, whatever cells I have selected, when I go to Data > Sort, it will always use the top most row as the header and the header description will be displayed in the "sort by" dropdown box

If i understand your reply correctly, it means that I do not select the first row.

(In my sheet, The first row is a sentence with the words "This sheet contains details of fruits."
From the second row onwards, it is like the rows as seen in Book1_rowrow.xls )