Forum Discussion
If a cell contains specific text, return this specific text otherwise leave blank
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
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 Replies
I was looking for a formula that would give me a similar result and came across this post. I changed the reference cell of B12, changed the "Monday" and the "non working day" to what I needed and it worked perfectly. Thanks for posting this. This is a great community.
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
Thank you, that formula works.
That was my only issue, I must have explained my issue poorly, thank you for your help.
