Date Comparisons With Time Limits

Biggs1001

New Member
Joined
Jan 16, 2019
Messages
3
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
B123451/1/19909/15/20007/30/2001
C123455/1/19987/11/2004
D123453/1/2000
E1234510/1/1997
F12345
G12345
H123458/1/19941/18/199911/11/1999
I1234512/1/20014/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

Thanks in advance!
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
46,416
Office Version
  1. 365
Platform
  1. Windows
How about
<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
Joined
Jan 16, 2019
Messages
3
How about
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
Joined
Jun 12, 2014
Messages
46,416
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,926
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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
Joined
Oct 24, 2012
Messages
3,926
Office Version
  1. 365
Platform
  1. MacOS
opps , went off for dinner and didn't refresh
 

Biggs1001

New Member
Joined
Jan 16, 2019
Messages
3
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

Well-known Member
Joined
Oct 24, 2012
Messages
3,926
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,109,122
Messages
5,526,956
Members
409,730
Latest member
mimipiz

This Week's Hot Topics

Top