Can anyone help me?
I have a programme of events spanning a whole month.
I have different groups of people attending some of the events on specific dates. Most groups attend for a few days.
There is a standard programme but changes can be made to the standard programme.
I would like to:
Input the dates a group will attend, e.g., 04/06/2022 - 08/06/2022 on a timeline.
If the dates they are attending are out of range, the cell should remain blank.
If the cell is within range, it activates a condition.
The condition being: Return the standard programme for that day. If there are changes to the programme, return this result instead.
=IF(AND(D1<$B$2,D1>$C$2),"",IF(D17<>"",D17,IF(AND(D1<$B$2,D1>$C$2),D12)))
The above being:
=IF(AND(D1<$B$2,D1>$C$2),"", Check the date in D1 is in range: start date in B2, end date in C2. If it isn't in range, leave the cell blank
IF(D17<>"",D17, If there have been programme changes in D17, return this value. If D17 is blank, do nothing
IF(AND(D1<$B$2,D1>$C$2),D12))) If date is in range and there have been no changes to the programme in D17, return the standard programme in D12
The condition seems to work but it's the initial date within range part which is problematic. If anyone has any thoughts, I'd be grateful.
I've also attached a picture of my spreadsheet. Thank you in advance.
I have a programme of events spanning a whole month.
I have different groups of people attending some of the events on specific dates. Most groups attend for a few days.
There is a standard programme but changes can be made to the standard programme.
I would like to:
Input the dates a group will attend, e.g., 04/06/2022 - 08/06/2022 on a timeline.
If the dates they are attending are out of range, the cell should remain blank.
If the cell is within range, it activates a condition.
The condition being: Return the standard programme for that day. If there are changes to the programme, return this result instead.
=IF(AND(D1<$B$2,D1>$C$2),"",IF(D17<>"",D17,IF(AND(D1<$B$2,D1>$C$2),D12)))
The above being:
=IF(AND(D1<$B$2,D1>$C$2),"", Check the date in D1 is in range: start date in B2, end date in C2. If it isn't in range, leave the cell blank
IF(D17<>"",D17, If there have been programme changes in D17, return this value. If D17 is blank, do nothing
IF(AND(D1<$B$2,D1>$C$2),D12))) If date is in range and there have been no changes to the programme in D17, return the standard programme in D12
The condition seems to work but it's the initial date within range part which is problematic. If anyone has any thoughts, I'd be grateful.
I've also attached a picture of my spreadsheet. Thank you in advance.