If a cell contains specific text, return this specific text otherwise leave blank

[Richie_M]

Hi,

I am using a pre-made excel spreadsheet for time sheets at work. I work 32 hours a week with Monday off. I am trying to insert an IF function that if the Day is 'Monday', return 'Non-Work Day'. 

I am currently using: 

=IF(B12="Monday","Non-Work Day," ") 

however all i get is a blank cell, even if the day is Monday. 

I believe the problem is  : the date column sets the day, so 1 July 2019 in cell C11 automatically changes the day in cell B11 to Monday, so the value in B11 is still equal to 1 July 2019, not Monday. (formula in B11 is '=c11)

Is there a way to get excel to read the output of 'Monday' instead of the input of '=c11' so I can match to 'Monday'? 

Many thanks 

[#2 · Accepted solution]

@Richie_M 

Hi

The setup is not clear to me. However to evaluate the Weekday of any date you can use one of 2 functions:

• Weekday function >> Returns a number from 1-7 (or 0 to 6)
• TEXT function >> Returns text based upon the formatting you specify in the second argument.

In your case , if you are evaluating the date in cell B12, then your function will be

=IF(TEXT(B12,"dddd")="Monday","Non Working Day","")

Your other issue of a cell referencing a wrong one, I need to check and audit your worksheet to fix it.

Hope that Helps

Nabil Mourad

[#3]

@nabilmourad 

Thank you, that formula works. 

That was my only issue, I must have explained my issue poorly, thank you for your help.