Checking a start and end date against a range of dates

[jasonkgreen]

I need a way for the cells under the dates to determine whether a calculation should be performed based on the Start Date. In the example below, there would be no calculations in Aug, Sept, or Oct. There would be calculations in Nov 2023 through July 2024 (end date) and then no calculations after July 2024. What is the best way to accomplish this?

Project Name	Duration (in mos)	Start Date​	End Date​	8/1/2023	9/1/2023	10/1/2023	11/1/2023	12/1/2023	1/1/2024	2/1/2024	3/1/2024
Project #1	8.00	11/06/23​	07/06/24​	No	No	No	Yes	Yes	Yes	Yes	Yes

 

[Trebor76]

Hi there,

Assuming the dates are actually dates (they should be naturally right justified in the cell if they are so I have my doubts about the dates in the heading) you could use this though you may have to change the range references to suit:

=IF(AND(E1>=$C$2,E1<=$D$2),"Yes","No")

Regards,

Robert

 

[jasonkgreen]

Hi there,

Assuming the dates are actually dates (they should be naturally right justified in the cell if they are so I have my doubts about the dates in the heading) you could use this though you may have to change the range references to suit:

=IF(AND(E1>=$C$2,E1<=$D$2),"Yes","No")

Regards,

Robert

Yes, this works. I just need to replace YES with my calculation. The dates are fine. Not sure why they showed as left-justified when I copied the table in.

Thanks for the help.

 

[Peter_SSs]

Yes, this works.

Are you sure?
Does that mean your shown values in post 1 were incorrect? Also your description which implies there would be a calculation in Nov?

In the example below, there would be no calculations in Aug, Sept, or Oct.

To match your examples and description, I think you might need something more like row 4 below. (My dates are d/m/y format)

23 11 07.xlsm
	B	C	D	E	F	G	H	I	J
1				1/08/2023	1/09/2023	1/10/2023	1/11/2023	1/12/2023	1/01/2024
2	Post 1 Desired	6/11/2023	6/07/2024	No	No	No	Yes	Yes	Yes
3	Post 2	6/11/2023	6/07/2024	No	No	No	No	Yes	Yes
4	My suggestion	6/11/2023	6/07/2024	No	No	No	Yes	Yes	Yes
Sheet2 (2)

Cell Formulas
Range	Formula
E3:J3	E3	=IF(AND(E$1>=$C3,E$1<=$D3),"Yes","No")
E4:J4	E4	=IF(AND(E$1>$C4-DAY($C4),E$1<=$D4),"Yes","No")