Previous Topic Next Topic

Convert a date field in excel to day of week

[Solved/Closed]
Report
-
 Indian -
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.


Related:

5 replies

Answer 1 / 5
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
 486
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
Thank you
Thank you

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

CCM 2821 users have said thank you to us this month

Answer 2 / 5
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
 486
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 !!
Answer 3 / 5
Posts
3
Registration date
Monday June 20, 2011
Status
Member
Last seen
June 23, 2011
When you format a cell use the reference "dddd", this will show you the full name of the day.
Answer 4 / 5

Trowa,

Brilliant! thank you so much for the IF statements. they worked perfect!
Answer 5 / 5

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