Date to Day of Week

[djeerd]

I have a column of Dates. How can I convert the date to the day of the week?

Example 8/23/11 = Tuesday.

 

[tweedle]

You can make a reference to the cell and format that reference as Custom, Type dddd

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 61px"><COL style="WIDTH: 61px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">1/1/2011</TD><TD style="TEXT-ALIGN: right">Saturday</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D5</TD><TD>=C5</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[djeerd]

Yes, good.

When I do this and have my columns of days of week and I try DATA.AUTOFILTER, it shows the dates to select not the day. I am trying to filter out the Mondays. Any other thoughts?

 

[tweedle]

Yeh, Formatting won't help filtering
Column D below will assist the filtering aspect.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 61px"><COL style="WIDTH: 88px"><COL style="WIDTH: 82px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Date</TD><TD>Formatted</TD><TD>WeekDay</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">1/1/2011</TD><TD style="TEXT-ALIGN: right">Saturday</TD><TD>Saturday</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">1/2/2011</TD><TD style="TEXT-ALIGN: right">Sunday</TD><TD>Sunday</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C5</TD><TD>=B5</TD></TR><TR><TD>D5</TD><TD>=LOOKUP(WEEKDAY(B5),{1,2,3,4,5,6,7},{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"})</TD></TR><TR><TD>C6</TD><TD>=B6</TD></TR><TR><TD>D6</TD><TD>=LOOKUP(WEEKDAY(B6),{1,2,3,4,5,6,7},{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"})</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[Excelestial]

Assuming your date is in cell M4:

=TEXT(DAY(M4+1),"dddd")

 

[djeerd]

Super! Many Thanks