Return specific text based on day of week found in another cell

[tlg]

I am building a spreadsheet where I require a specific line of text which differs based on the day of the week which is shown in text in a cell at the top of the page.

The day and date in C1 is copied from the date which is input into A1 on the first sheet only, so on the Sheet2 it will use =SUM(Sheet1!A1)

So, if the day in C1 is a "Monday", I would like the text in B4 to show "Ensure all passes are validated"

The text in B4 will then change based on the day which is in C1 as follows:

"Monday" - "Ensure all passes are validated"
"Tuesday" - "Check gate 3C"
"Wednesday" - "Clear contents of all boxes"
"Thursday" - "Check door 9"
"Friday" - "Conduct vehicle checks"

I have tried a few options but can't get the correct outcome, not sure if it is due to the date being fed from another sheet?

I would really appreciate any help, I hope my explanation of what I am trying to achieve is clear.

 

[JoeMo]

Book4
	A	B	C	D	E	F
1			Friday, April 28, 2023
2
3
4		Conduct vehicle checks
REPORT_APR

Cell Formulas
Range	Formula
B4	B4	=CHOOSE(WEEKDAY(C1,2),"Ensure all passes are validated","Check gate 3C","Clear contents of all boxes","Check door 9","Conduct vehicle checks")

 

[tlg]

Brilliant, that works perfectly, thank you for your help!

If I had a task that only happened once a month, for example on the first Monday of the month only, could this be added or am I making this too complicated?

 

[tlg]

Another question, if the outcome of B4 is blank (as in there is no task on a Saturday or Sunday for example), how can I remove the "Specific Day Action" text in A4 cell. I have tried to use conditional formatting to change the font colour to white if B4 is blank but this does not work, possibly because B4 contains a formula.

Thanks again for your help.

 

[JoeMo]

Another question, if the outcome of B4 is blank (as in there is no task on a Saturday or Sunday for example), how can I remove the "Specific Day Action" text in A4 cell. I have tried to use conditional formatting to change the font colour to white if B4 is blank but this does not work, possibly because B4 contains a formula.

Thanks again for your help.

You are welcome. I've modified the formula and introduced conditional formatting below to address your additional requests. B4 will appear blank and the text in A4 will be "whited out" when dates in C1 are for weekend days. The conditional format is set to produce a white color for the font in A4.

Book4
	A	B	C	D	E	F
1			Friday, April 28, 2023
2
3
4	Specific Day Action	Conduct vehicle checks
REPORT_APR

Cell Formulas
Range	Formula
B4	B4	=IFERROR(CHOOSE(WEEKDAY(C1,2),"Ensure all passes are validated","Check gate 3C","Clear contents of all boxes","Check door 9","Conduct vehicle checks"),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4	Expression	=$B$4=""	text	NO

 

[tlg]

You are welcome. I've modified the formula and introduced conditional formatting below to address your additional requests. B4 will appear blank and the text in A4 will be "whited out" when dates in C1 are for weekend days. The conditional format is set to produce a white color for the font in A4.

Book4
	A	B	C	D	E	F
1			Friday, April 28, 2023
2
3
4	Specific Day Action	Conduct vehicle checks
REPORT_APR

Cell Formulas
Range	Formula
B4	B4	=IFERROR(CHOOSE(WEEKDAY(C1,2),"Ensure all passes are validated","Check gate 3C","Clear contents of all boxes","Check door 9","Conduct vehicle checks"),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4	Expression	=$B$4=""	text	NO

Excellent, works perfect, thanks again.

Is there a way of adding a separate box for a task to be displayed once a month? I have a task that only needs to be done on the first Monday of the month.

 

[JoeMo]

Excellent, works perfect, thanks again.

Is there a way of adding a separate box for a task to be displayed once a month? I have a task that only needs to be done on the first Monday of the month.

You are welcome.
Not sure what you mean by "separate box"? How about putting the Monthly task message in another cell like B3 as below that only appears on the first Monday of the month?

Book4
	A	B	C	D	E	F
1			Monday, April 3, 2023
2
3	Monthly Task	Monthly Task Message
4	Specific Day Action	Ensure all passes are validated
REPORT_APR

Cell Formulas
Range	Formula
B3	B3	=IF(AND(WEEKDAY(C1,2)=1,DAY(C1)<=7),"Monthly Task Message","")
B4	B4	=IFERROR(CHOOSE(WEEKDAY(C1,2),"Ensure all passes are validated","Check gate 3C","Clear contents of all boxes","Check door 9","Conduct vehicle checks"),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4	Expression	=$B$4=""	text	NO

 

[tlg]

You are welcome.
Not sure what you mean by "separate box"? How about putting the Monthly task message in another cell like B3 as below that only appears on the first Monday of the month?

Book4
	A	B	C	D	E	F
1			Monday, April 3, 2023
2
3	Monthly Task	Monthly Task Message
4	Specific Day Action	Ensure all passes are validated
REPORT_APR

Cell Formulas
Range	Formula
B3	B3	=IF(AND(WEEKDAY(C1,2)=1,DAY(C1)<=7),"Monthly Task Message","")
B4	B4	=IFERROR(CHOOSE(WEEKDAY(C1,2),"Ensure all passes are validated","Check gate 3C","Clear contents of all boxes","Check door 9","Conduct vehicle checks"),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4	Expression	=$B$4=""	text	NO

Thanks, it works perfectly, I have tried to change the formula to get the same result for the second Monday of the month but can't seem to get the right result.

Is there any chance you can explain what I need to change to give me the second Monday of the month please?

Thanks again.

 

[JoeMo]

Thanks, it works perfectly, I have tried to change the formula to get the same result for the second Monday of the month but can't seem to get the right result.

Is there any chance you can explain what I need to change to give me the second Monday of the month please?

Thanks again.

Here's the change to go in cell B3 in the example of post #7:

=IF(AND(WEEKDAY(C1,2)=1,DAY(C1)>7,DAY(C1)<= 14),"Monthly Task Message","")

 

[tlg]

Here's the change to go in cell B3 in the example of post #7:

=IF(AND(WEEKDAY(C1,2)=1,DAY(C1)>7,DAY(C1)<= 14),"Monthly Task Message","")

Excellent, thank you so much for your help!