Date Comparisons With Time Limits

[Biggs1001]

Hey all,


I did a search but the 'If' function is able to encompass so much I wasn't successful in finding what I'm looking to do - forgive me if I just missed it (or I'm not looking for the right function to efficiently show what I need - a real possibility!)

I have a list of projects that I need to find out what projects have a Ground Breaking Date and of those that have a Ground Breaking Date, which ones haven't completed construction yet - but with all of this I need to figure out what date would be 10 years post Ground Breaking Date of those that fit the above bill.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Project Number
[/TD]
[TD]Ground Breaking Date
[/TD]
[TD]Construction End Date
[/TD]
[TD]Grand Opening Date
[/TD]
[/TR]
[TR]
[TD]A12345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B12345[/TD]
[TD]1/1/1990[/TD]
[TD]9/15/2000[/TD]
[TD]7/30/2001[/TD]
[/TR]
[TR]
[TD]C12345[/TD]
[TD]5/1/1998[/TD]
[TD]7/11/2004[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D12345[/TD]
[TD]3/1/2000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E12345[/TD]
[TD]10/1/1997[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F12345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G12345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]H12345[/TD]
[TD]8/1/1994[/TD]
[TD]1/18/1999[/TD]
[TD]11/11/1999[/TD]
[/TR]
[TR]
[TD]I12345[/TD]
[TD]12/1/2001[/TD]
[TD]4/14/2008[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So in the above table I want to add a column after the Grand Opening Date that I would put my function into that would look at the Ground Breaking column and see that projects D12345 and E12345 both have Ground Breaking Dates yet no Construction End Dates, would flag that with the date that is 10 years from the Ground Breaking Date. The other projects would be ignored (as they either do not have a Ground Breaking Date or they have one but have a Construction End Date as well)

Is the If function what I'm needing?

Any help would be appreciated - I can do it by hand and just fill in those that need a 10 year date but with 1400 lines of projects, I'm hoping there's a more efficient way

Thanks in advance!

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E
1	Project Number	Ground Breaking Date	Construction End Date	Grand Opening Date	10 years
2	A12345
3	B12345	01/01/1990	15/09/2000	30/07/2001
4	C12345	01/05/1998	11/07/2004
5	D12345	01/03/2000			01/03/2010
6	E12345	01/10/1997			01/10/2007
7	F12345
8	G12345
9	H12345	01/08/1994	18/01/1999	11/11/1999
10	I12345	01/12/2001	14/04/2008
Sheet1

Cell Formulas
Range	Formula
E2	=IF(B2="","",IF(C2="",DATE(YEAR(B2)+10,MONTH(B2),DAY(B2)),""))

 

[Biggs1001]

How about
Excel 2013/2016

	A	B	C	D	E
Project Number	Ground Breaking Date	Construction End Date	Grand Opening Date	10 years
A12345
B12345
C12345
D12345
E12345
F12345
G12345
H12345
I12345

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]01/01/1990[/TD]
[TD="align: right"]15/09/2000[/TD]
[TD="align: right"]30/07/2001[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]01/05/1998[/TD]
[TD="align: right"]11/07/2004[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]01/03/2000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/03/2010[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]01/10/1997[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/10/2007[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]01/08/1994[/TD]
[TD="align: right"]18/01/1999[/TD]
[TD="align: right"]11/11/1999[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]01/12/2001[/TD]
[TD="align: right"]14/04/2008[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IF(B2="","",IF(C2="",DATE(YEAR(B2)+10,MONTH(B2),DAY(B2)),""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

You're amazing Fluff - thanks!

 

[Fluff]

You're welcome & thanks for the feedback

 

[etaf]

Ground Breaking Date and of those that have a Ground Breaking Date, which ones haven't completed construction yet

you can use
=AND(B2<>"",C2="")
to test if there is a Ground Breaking Date but NO completed construction

So we could out that into an IF test

=IF( AND(B2<>"",C2="") , If true then add 10 years to Ground Breaking Date B2

=date(Year(B2)+10, Month(B2), Day(B2))

if its not true then leave blank

would
=IF( AND(B2<>"",C2="") , date(Year(B2)+10, Month(B2), Day(B2)) ,"")
work

 

[etaf]

opps , went off for dinner and didn't refresh

 

[Biggs1001]

you can use
=AND(B2<>"",C2="")
to test if there is a Ground Breaking Date but NO completed construction

So we could out that into an IF test

=IF( AND(B2<>"",C2="") , If true then add 10 years to Ground Breaking Date B2

=date(Year(B2)+10, Month(B2), Day(B2))

if its not true then leave blank

would
=IF( AND(B2<>"",C2="") , date(Year(B2)+10, Month(B2), Day(B2)) ,"")
work

Thanks for taking the time to help out E!

 

[etaf]

you are welcome