Another Excel Quest

[bigmack]

http://i165.photobucket.com/albums/u70/macktime/1_14_10_15_58_50.png

I need to sort by date. I need to format so the test dates reads Friday November 20, 2009. When I sort in its current form it puts 12 before 9.

[BillW]

Just a WAG - is the column data type set to "Date"?

[bigmack]

Yes. I'm trying to set the entire column to the second date format but it won't take.

http://i165.photobucket.com/albums/u70/macktime/1_14_10_16_22_17.png

[HUSKER55]

JUST A THOUGHT, PROBABLY WRONG


I noticed that you have the word "total" in the column. Excel will read that cell as text and will not convert the date to a number. That might throw everything outta whack

good luck

[raybo]

Yes. I'm trying to set the entire column to the second date format but it won't take.

http://i165.photobucket.com/albums/u70/macktime/1_14_10_16_22_17.png

Maybe because there's an asterisk in front of that date format? Check your OS regional date and time settings. This format may not be allowed with your OS.

[raybo]

I tried doing what you want, in Excel 2007, and had no problem with either the date format or the sort. I just selected the test dates, right clicked, selected format, selected date, selected the date format you did, and clicked ok. Worked fine.

For the sort, I selected the range, to include all columns, starting with the first row of data (below the headers). Then went to the sort dialogue and selected: sort by (test date column), selected:by cell value, selected: newest to oldest. Worked fine, all columns sorted from newest date to oldest date, it left the "Total" row where it was.

[Tom]

I have been able to convert to date format using the "test to columns" feature.
Select fixed width, put a line right after thelast character, then choose date format. Once you get to a date format, changing to any other one seems to be possible.

[Ray2000]

You might try this if you haven't solved it already...



Select all columns

Remove quick filter

(data-sort and filter- click on funnel)


select column(s)

format to date

select all columns

re-install quick filter



(you were only formating filtered rows)

[Ray2000]

Sorry, may not work

I was trying to get around the fix of inserting a new column and use the formula:

=datevalue(E2)

and fill down to last row

then copy new column and 'paste special' (values only) right back into the new column. (IMPORTANT)

Then delete Col E insert new Col E and format to your liking.

Then copy and paste values from new col back into new "E" col

Delete new Column

(Back up sheet first)

[hcap]

Mack,

Check your email.

[bigmack]

Mack,

Check your email.
I don't care what people say about you, in my book you're AOK.

Raybo mentioned you were an ace with excel. If the cure you mentioned doesn't work however, we go back to being archenemies. :D

Thanks to all. It's the love around here that keeps me coming back. :rolleyes:

[hcap]

I just looked at Ray 2000's response and the = datevalue function will work as well.

As I mentioned in my email, your imported or copied data is the culprit.
And others have had similar problems with dates copied/imported from other programs or sources into excel.

If the cure you mentioned doesn't work however, we go back to being archenemies. :D
This does mean I can go back to posting that dancing cheeseburger ?? :rolleyes:

[Tom]

I don't care what people say about you, in my book you're AOK.

Raybo mentioned you were an ace with excel. If the cure you mentioned doesn't work however, we go back to being archenemies. :D

Thanks to all. It's the love around here that keeps me coming back. :rolleyes:


Yeah, he helped me out too. Nice guy! :ThmbUp:

[bigmack]

This does mean I can go back to posting that dancing cheeseburger ??
You know, & I know, & the American people know that it is far more than a mere cheeseburger.

At last count it's, two all beef patties, special sauce, lettuce, cheese, pickles, onions on a sesame seed bun. (said in haste)

Good day to you sir...

[hcap]

I just sent you a correction.
Will allow you to sort, but your subtotals i.e. "55796 Total" in column A interfere with proper sorts.

My screwed up Norton internet security new-fangled crap program is playing games with attachments.
Is taking forever to do email. Let me know if you get the new file.
Time to go back to AVG

PS: Cheeseburgers may be all American, but not when they do the "hoola"
Especially wearing seeded buns.

[bigmack]

I just sent you a correction.
Will allow you to sort, but your subtotals i.e. "55796 Total" in column A interfere with proper sorts.
You da man! Don't go changin'.

Just a reminder (http://s0.ilike.com/play#Joe+Cocker:You+Are+So+Beautiful:38516:s66717. 6487.64798) :p

Much obliged Hombre.

[hcap]

No problemo.

:cool: