Convert a date field in excel to day of week

Solved/Closed
C - Feb 23, 2011 at 06:57 PM
 Indian - Jul 7, 2013 at 09:55 AM
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.


5 replies

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Feb 25, 2011 at 09:53 AM
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
7
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Feb 24, 2011 at 10:29 AM
Hi C,

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

Best regards,
Trowa
6
thank you for taking the time to answer this. I knew about formatting the cell that way to get the day of week. My question is how can I make the day of week something that I can sort on so, as example, I want ALL mondays grouped together so I would sort on the day of week. you cant do this with format cell, it still has the date for the actual value it just shows the day of week

thanks
0
Format the Cell as "ddd", copy the entire column to Notepad, select the pasted data in Notepad and copy -> paste it in excel.. Bingo !!
0
shameem007
Posts
3
Registration date
Monday June 20, 2011
Status
Member
Last seen
June 23, 2011

Jun 23, 2011 at 01:02 AM
When you format a cell use the reference "dddd", this will show you the full name of the day.
3
Trowa,

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

Didn't find the answer you are looking for?

Ask a question
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!
1