Nested function: If a date is not within a date range, leave blank. If it is within a date range, return a result based on a condition

MrOllyR

New Member
Joined
Jun 24, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • excel.png
    excel.png
    27.7 KB · Views: 23

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
=IF(AND(D1<$B$2,D1>$C$2),"",IF(D17<>"",D17,IF(AND(D1<$B$2,D1>$C$2),D12)))

AND(D1<$B$2,D1>$C$2),""
same as
AND(D1<$B$2,D1>$C$2),D12

so if the date is NOT in range , then leave blank
If it is in range
then check D17 - if D17 is blank
then check again if in range , which it is , otherwise we would not be this far in the nested IF , so why check again
=IF(AND(D1<$B$2,D1>$C$2),"",IF(D17<>"",D17,D12))

as its in range, tested in first AND() , and if D17 is blank, then just return D12

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
 
Upvote 0
=IF(AND(D1<$B$2,D1>$C$2),"",IF(D17<>"",D17,IF(AND(D1<$B$2,D1>$C$2),D12)))

AND(D1<$B$2,D1>$C$2),""
same as
AND(D1<$B$2,D1>$C$2),D12

so if the date is NOT in range , then leave blank
If it is in range
then check D17 - if D17 is blank
then check again if in range , which it is , otherwise we would not be this far in the nested IF , so why check again
=IF(AND(D1<$B$2,D1>$C$2),"",IF(D17<>"",D17,D12))

as its in range, tested in first AND() , and if D17 is blank, then just return D12

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Thank you for your reply. Here's a link to a sample spreadsheet: Loading Google Sheets

If you have any advice on what I need to do in order to ensure cells not within the date range remain blank and the condition returns the result I need, I would be grateful.
 
Upvote 0
Thank you for your reply. Here's a link to a sample spreadsheet: Loading Google Sheets

If you have any advice on what I need to do in order to ensure cells not within the date range remain blank and the condition returns the result I need, I would be grateful.
Just as a further clarification: If you are able to open the spreadsheet, D2, L2 and M2 should be blank as they are not in range. Any cell returning FALSE should return the standard programme for that day.
 
Upvote 0
did you try my formula ?

what are the results you expected - just seen your reply

Excel-ETAF.xlsx
ABCDEFGHIJKLMN
1Group nameStart dateEnd date6/1/226/2/226/3/226/4/226/5/226/6/226/7/226/8/226/9/226/10/22
2Group A6/4/226/8/22meetingexcursionlessonstheatre visitexcursionexcursionlessonslessonslessonslessons
3`
4
5
6
7
8
9
10Standard programme
116/1/226/2/226/3/226/4/226/5/226/6/226/7/226/8/226/9/226/10/22
12lessonsexcursionlessonslessonsexcursionexcursionlessonslessonslessonslessons
13
14
15Programme changes
166/1/226/2/226/3/226/4/226/5/226/6/226/7/226/8/226/9/226/10/22
17meetingtheatre visit
18
Sheet1
Cell Formulas
RangeFormula
D2:M2D2=IF(AND(D1<$B$2,D1>$C$2),"",IF(D17<>"",D17,D12))


 
Upvote 0
ok,
I have change the formula around to test if in RANGE

=IF(AND(D1>=$B$2,D1<=$C$2),IF(D17<>"",D17,D12),"")

otherwise it will never be true
AND(D1<$B$2,D1>$C$2)



Excel-ETAF-1.xlsx
ABCDEFGHIJKLM
1Group nameStart dateEnd date6/1/226/2/226/3/226/4/226/5/226/6/226/7/226/8/226/9/226/10/22
2Group A6/4/226/8/22   theatre visitexcursionexcursionlessonslessons  
3
4
5
6
7
8
9
10Standard programme
116/1/226/2/226/3/226/4/226/5/226/6/226/7/226/8/226/9/226/10/22
12lessonsexcursionlessonslessonsexcursionexcursionlessonslessonslessonslessons
13
14
15Programme changes
166/1/226/2/226/3/226/4/226/5/226/6/226/7/226/8/226/9/226/10/22
17meetingtheatre visit
18
19
20FALSEFALSEFALSETRUETRUETRUETRUETRUEFALSEFALSE
21
Sheet1
Cell Formulas
RangeFormula
D2:M2D2=IF(AND(D1>=$B$2,D1<=$C$2),IF(D17<>"",D17,D12),"")
D20:M20D20=AND(D1>=$B$2,D1<=$C$2)




 
Upvote 0
Solution
ok,
I have change the formula around to test if in RANGE

=IF(AND(D1>=$B$2,D1<=$C$2),IF(D17<>"",D17,D12),"")

otherwise it will never be true
AND(D1<$B$2,D1>$C$2)



Excel-ETAF-1.xlsx
ABCDEFGHIJKLM
1Group nameStart dateEnd date6/1/226/2/226/3/226/4/226/5/226/6/226/7/226/8/226/9/226/10/22
2Group A6/4/226/8/22   theatre visitexcursionexcursionlessonslessons  
3
4
5
6
7
8
9
10Standard programme
116/1/226/2/226/3/226/4/226/5/226/6/226/7/226/8/226/9/226/10/22
12lessonsexcursionlessonslessonsexcursionexcursionlessonslessonslessonslessons
13
14
15Programme changes
166/1/226/2/226/3/226/4/226/5/226/6/226/7/226/8/226/9/226/10/22
17meetingtheatre visit
18
19
20FALSEFALSEFALSETRUETRUETRUETRUETRUEFALSEFALSE
21
Sheet1
Cell Formulas
RangeFormula
D2:M2D2=IF(AND(D1>=$B$2,D1<=$C$2),IF(D17<>"",D17,D12),"")
D20:M20D20=AND(D1>=$B$2,D1<=$C$2)




I can't thank you enough. Totally makes sense and just got round to adding this into my spreadsheet. Thank you once again.
 
Upvote 0
ok,
I have change the formula around to test if in RANGE

=IF(AND(D1>=$B$2,D1<=$C$2),IF(D17<>"",D17,D12),"")

otherwise it will never be true
AND(D1<$B$2,D1>$C$2)



Excel-ETAF-1.xlsx
ABCDEFGHIJKLM
1Group nameStart dateEnd date6/1/226/2/226/3/226/4/226/5/226/6/226/7/226/8/226/9/226/10/22
2Group A6/4/226/8/22   theatre visitexcursionexcursionlessonslessons  
3
4
5
6
7
8
9
10Standard programme
116/1/226/2/226/3/226/4/226/5/226/6/226/7/226/8/226/9/226/10/22
12lessonsexcursionlessonslessonsexcursionexcursionlessonslessonslessonslessons
13
14
15Programme changes
166/1/226/2/226/3/226/4/226/5/226/6/226/7/226/8/226/9/226/10/22
17meetingtheatre visit
18
19
20FALSEFALSEFALSETRUETRUETRUETRUETRUEFALSEFALSE
21
Sheet1
Cell Formulas
RangeFormula
D2:M2D2=IF(AND(D1>=$B$2,D1<=$C$2),IF(D17<>"",D17,D12),"")
D20:M20D20=AND(D1>=$B$2,D1<=$C$2)




Fantastic! Thank you!
 
Upvote 0
I can't thank you enough. Totally makes sense and just got round to adding this into my spreadsheet. Thank you once again.
I switched the marked solution post accordingly.

@MrOllyR - in your future questions, please mark the post that answered your question as the solution instead of your feedback post as it will help future readers.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top