Convert a date field in excel to day of weekSolved/Closed

Question

Hello, 
I know how to format a cell with a date in it so that it shows the day of the week.  WHat I am trying to do is sort on the day of the week so that all mondays are lumped together, etc.  I guess I need to know if I can turn the results that show in the cell (day of week) into a literal that simply reads monday and I then can sort it.

any help is appreciated.


Configuration: Windows XP / Firefox 3.6

TrowaD · Accepted Answer

Sorry C, didn't realize you couldn't sort your data like this.

You could use Filter. Top menu > data > filter > AutoFilter. Now your are able to group one type of day together, giving you a chance to copy/paste for a fixed list.

You could also use two extra columns:
One column to put this formula in:
=IF(WEEKDAY(A2)=1,"Sunday",IF(WEEKDAY(A2)=2,"Monday",IF(WEEKDAY(A2)=3,"Tuesday",IF(WEEKDAY(A2)=4,"Wednesday",IF(WEEKDAY(A2)=5,"Thursday",IF(WEEKDAY(A2)=6,"Friday","Saturday"))))))
Second column to paste special the data from the formula column as value.
Now you can sort your data as you are used to.
Note: that any unwanted columns can be hidden for a cleaner looking sheet.

Best regards,
Trowa

TrowaD · Answer

Hi C,

When you format a cell use the reference "dddd", this will show you the full name of the day.

Best regards,
Trowa

C · Answer

Trowa,

Brilliant!  thank you so much for the IF statements.  they worked perfect!

crywolf · Answer

Thanks for posting the code. I ran into the same issue as C. I could not filder my spreadsheet by dates. I'm a happy man once again!

shameem007 · Answer

When you format a cell use the reference "dddd", this will show you the full name of the day.

Convert a date field in excel to day of week

5 responses

Similar discussions

Subscribe To Our Newsletter!