Project falls between 2 dates

[1excel1]

Hi, I need some help with a date formula. I need the formula to output a '1' if the project falls within Start Date and End Date. If there's no date, then it should be Today's date.

Kind regards,

 

[Peter_SSs]

Welcome to the MrExcel board!

Different Excel versions have different functions & features available to them. By choosing "Prefer Not To Say" for your version you are stopping us knowing which functions/features are available to you and therefore it is difficult for us to know what to suggest. Perhaps you could reconsider that option in your profile?


Also, it is much easier for helpers to test what might be best for your data if they can actually copy that data to their own worksheets.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[1excel1]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Project ID	Start Date	End Date	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24
2	A	02/10/2021
3	B	03/10/2023
4	C	21/09/2024
5	D	17/07/2024	01/08/2024
Sheet1

 

[Peter_SSs]

Thanks for updating your profile and for the XL2BB sample data.

See if this could work for you.

24 10 07.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Project ID	Start Date	End Date	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24
2	A	2/10/2021		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
3	B	3/10/2023				1	1	1	1	1	1	1	1	1	1	1	1	1
4	C	21/09/2024															1	1
5	D	17/07/2024	1/08/2024												1	1
1excel1

Cell Formulas
Range	Formula
D2:R5	D2	=IF(AND(D$1>=$B2-DAY($B2)+1,D$1<=IF($C2="",TODAY(),$C2)),1,"")

 

[1excel1]

Thanks for updating your profile and for the XL2BB sample data.

See if this could work for you.

24 10 07.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Project ID	Start Date	End Date	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24
2	A	2/10/2021		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
3	B	3/10/2023				1	1	1	1	1	1	1	1	1	1	1	1	1
4	C	21/09/2024															1	1
5	D	17/07/2024	1/08/2024												1	1
1excel1

Cell Formulas
Range	Formula
D2:R5	D2	=IF(AND(D$1>=$B2-DAY($B2)+1,D$1<=IF($C2="",TODAY(),$C2)),1,"")

Many thanks for this. It works!

 

[Peter_SSs]

Many thanks for this. It works!

You're welcome. Thanks for the follow-up.