SOLVED

# Formula to match Day of Week Name

Copper Contributor

# Formula to match Day of Week Name

I want to get the day of the week from a date and then evaluate if that matches a cell with different days of the week.  8/28/2023 is a Monday (text(cell,"dddd")  I want the formula to indicat there is one occurrence of Monday in the dayofweek cell, result would be 1.  TY

2 Replies
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Formula to match Day of Week Name

For older versions of Excel, you may try the following formula...

=(LEN(A2)-LEN(SUBSTITUTE(A2,TEXT(\$B\$1,"dddd"),"")))/LEN(TEXT(\$B\$1,"dddd"))

For Excel 365, you may also try the following formula...

=SUM((TEXTSPLIT(A2,"; ")=TEXT(\$B\$1,"dddd"))*1)

# Re: Formula to match Day of Week Name

Thank you!
1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Formula to match Day of Week Name

For older versions of Excel, you may try the following formula...

=(LEN(A2)-LEN(SUBSTITUTE(A2,TEXT(\$B\$1,"dddd"),"")))/LEN(TEXT(\$B\$1,"dddd"))

For Excel 365, you may also try the following formula...

=SUM((TEXTSPLIT(A2,"; ")=TEXT(\$B\$1,"dddd"))*1)