Formula for calculating and round date

[Excel Burnout]

Hello!

The easiest way I can explain my conundrum is to provide a visual.

Cell- A1 has a date of 3/15/1950

Cell-B1 has number of 65

Cell-C1 has a date of 1/1 (no year)

Cell-E1 formula

What would the formula be to add the years from B1 to A1 and round the date up to the month and day put in C1?

I am looking for E1 to have the answer 1/1/2016.
3/15/1950+65 years rounded to the next January 1.

Thanks in advance!

 

[GraH - Guido]

Hi Excel Burnout,
would this work for you?
=EDATE(DATE(YEAR(A1),MONTH(C1),DAY(C1)),((MONTH(C1)<MONTH(A1))+B1)*12)

 

[Chihiro]

Assuming C1 has 1/1 (but has underlying date value with year).
=DATE(YEAR(A1)+B1+1,MONTH(C1),DAY(C1))

 

[John Jairo V]

Hi, to all!

Seems it depends on date in C1, and if month and day is less than or equal to month and day of C1 then stay in same year ( Check it, @GraH - Guido and @Chihiro ). Then, you can use:

=DATE(YEAR(A1)+B1+(TEXT(A1,"mmdd")>TEXT(C1,"mmdd")),MONTH(C1),DAY(C1))

or this:

=--(YEAR(A1)+B1+(TEXT(A1,"mmdd")>TEXT(C1,"mmdd"))&TEXT(C1,"-m-d"))

Blessings!

 

[bosco_yip]

Or,

=0+(C1&("/"&YEAR(A1)+B1+(TEXT(A1,"m/d")>TEXT(C1,"m/d"))))

Regards
Bosco

 

[Excel Burnout]

That worked perfectly! Thank you so much!

 

[Excel Burnout]

Worked perfectly! Thanks so much!

 

[Excel Burnout]

Hi Excel Burnout,
would this work for you?
=EDATE(DATE(YEAR(A1),MONTH(C1),DAY(C1)),((MONTH(C1)<MONTH(A1))+B1)*12)

How would I go about writing the formula if I want the cell the cell that holds the equation to remain blank until the other data points have entries?

 

[Chihiro]

Nest it in IF().

Ex:
=IF(C1="","",EDATE(DATE(YEAR(A1),MONTH(C1),DAY(C1)),((MONTH(C1)<MONTH(A1))+B1)*12) )
Or
=IF(COUNTA(A1:C1)<3,"",EDATE(DATE(YEAR(A1),MONTH(C1),DAY(C1)),((MONTH(C1)<MONTH(A1))+B1)*12) )

 

[Excel Burnout]

For

Nest it in IF().

Ex:
=IF(C1="","",EDATE(DATE(YEAR(A1),MONTH(C1),DAY(C1)),((MONTH(C1)<MONTH(A1))+B1)*12) )
Or
=IF(COUNTA(A1:C1)<3,"",EDATE(DATE(YEAR(A1),MONTH(C1),DAY(C1)),((MONTH(C1)<MONTH(A1))+B1)*12) )

For some reason neither of these work, I am getting formula errors.
Here is the actual formula, without the language to hide when the data point cells are empty. How would I re-write it to hide the calculation?



=if(D10””,”",EDATE(DATE(YEAR('AB Policy Review'!D10),MONTH('AB Policy Review'!D43),DAY('AB Policy Review'!D43)),((MONTH('AB Policy Review'!D43)<MONTH('AB Policy Review'! D10))+'Percentage'!I26)*12)

 

[Chihiro]

Well, you are not referencing correct sheet and missing = sign.

=if('AB Policy Review'! D10 =””,"",...)

Or if you need 'Percentage'!I26 to be filled....
=IF('Percentage'!I26 ="","",....)

 

[Excel Burnout]

Well, you are not referencing correct sheet and missing = sign.

=if('AB Policy Review'! D10 =””,"",...)

Or if you need 'Percentage'!I26 to be filled....
=IF('Percentage'!I26 ="","",....)

'Percentage'!I26 is the field where the formula resides, and the cell I would like to remain blank until the other parameters have data.

 

[GraH - Guido]

@bosco_yip and @John Jairo V , I never would think to use ">" to compare text. Again some clever formulae constructs of both. Thank you.