Menu
Ask a question Report

Convert a date field in excel to day of week [Solved/Closed]

- Feb 23, 2011 at 06:57 PM - Latest reply:  Indian - Jul 7, 2013 at 09:55 AM
Excel Ask a question Report
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.


See more 

7 replies

Best answer
Report Respond to TrowaD
Posts
2448
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
November 20, 2018
 Feb 25, 2011 at 09:53 AM
7
Thank you
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

Thank you, TrowaD 7

Something to say? Add comment

CCM has helped 1699 users this month

Report Respond to TrowaD
Posts
2448
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
November 20, 2018
 Feb 24, 2011 at 10:29 AM
6
Thank you
Hi C,

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

Best regards,
Trowa
Report
C - Feb 24, 2011 at 03:32 PM
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
Report
Indian - Jul 7, 2013 at 09:55 AM
Format the Cell as "ddd", copy the entire column to Notepad, select the pasted data in Notepad and copy -> paste it in excel.. Bingo !!
Report Respond to shameem007
Posts
3
Registration date
Monday June 20, 2011
Last seen
June 23, 2011
 Jun 23, 2011 at 01:02 AM
3
Thank you
When you format a cell use the reference "dddd", this will show you the full name of the day.
Report Respond to C
Feb 25, 2011 at 10:16 AM
1
Thank you
Trowa,

Brilliant! thank you so much for the IF statements. they worked perfect!
Report Respond to crywolf
Jun 17, 2011 at 05:09 PM
1
Thank you
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!
Ask a question