Menu
Ask a question Report

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

- - 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 

5 replies

Best answer
Report
Posts
2542
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2019
 369
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

Say "Thank you" 7

A few words of thanks would be greatly appreciated. Add comment

CCM 6328 users have said thank you to us this month

Report
Posts
2542
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2019
 369
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
-
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
-
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
Posts
3
Registration date
Monday June 20, 2011
Status
Member
Last seen
June 23, 2011
3
Thank you
When you format a cell use the reference "dddd", this will show you the full name of the day.
Report
1
Thank you
Trowa,

Brilliant! thank you so much for the IF statements. they worked perfect!
Report
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