# Date Comparisons With Time Limits

#### Biggs1001

##### New Member
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.

 Project Number Ground Breaking Date Construction End Date Grand Opening Date A12345 B12345 1/1/1990 9/15/2000 7/30/2001 C12345 5/1/1998 7/11/2004 D12345 3/1/2000 E12345 10/1/1997 F12345 G12345 H12345 8/1/1994 1/18/1999 11/11/1999 I12345 12/1/2001 4/14/2008

<tbody>
</tbody>

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

### 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.

#### Fluff

##### MrExcel MVP, Moderator
<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">Project Number</td><td style="font-weight: bold;;">Ground Breaking Date</td><td style="font-weight: bold;;">Construction End Date</td><td style="font-weight: bold;;">Grand Opening Date</td><td style=";">10 years</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">A12345</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">B12345</td><td style="text-align: right;;">01/01/1990</td><td style="text-align: right;;">15/09/2000</td><td style="text-align: right;;">30/07/2001</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">C12345</td><td style="text-align: right;;">01/05/1998</td><td style="text-align: right;;">11/07/2004</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">D12345</td><td style="text-align: right;;">01/03/2000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">01/03/2010</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">E12345</td><td style="text-align: right;;">01/10/1997</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">01/10/2007</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">F12345</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">G12345</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">H12345</td><td style="text-align: right;;">01/08/1994</td><td style="text-align: right;;">18/01/1999</td><td style="text-align: right;;">11/11/1999</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">I12345</td><td style="text-align: right;;">01/12/2001</td><td style="text-align: right;;">14/04/2008</td><td style="text-align: right;;"></td><td style=";"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=IF(<font color="Blue">B2="","",IF(<font color="Red">C2="",DATE(<font color="Green">YEAR(<font color="Purple">B2</font>)+10,MONTH(<font color="Purple">B2</font>),DAY(<font color="Purple">B2</font>)</font>),""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

#### Biggs1001

##### New Member
Excel 2013/2016
ABCDE
1Project NumberGround Breaking DateConstruction End DateGrand Opening Date10 years
2A12345
3B1234501/01/199015/09/200030/07/2001
4C1234501/05/199811/07/2004
5D1234501/03/200001/03/2010
6E1234501/10/199701/10/2007
7F12345
8G12345
9H1234501/08/199418/01/199911/11/1999
10I1234501/12/200114/04/2008

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
E2=IF(B2="","",IF(C2="",DATE(YEAR(B2)+10,MONTH(B2),DAY(B2)),""))

<tbody>
</tbody>

<tbody>
</tbody>

You're amazing Fluff - thanks!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

#### etaf

##### Well-known Member

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

##### Well-known Member
opps , went off for dinner and didn't refresh

#### Biggs1001

##### New Member
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!

you are welcome

Replies
7
Views
88
Replies
4
Views
184
Replies
6
Views
69
Replies
2
Views
89
Replies
16
Views
127