Calculate Date In Excel

[Excel Burnout]

Hello,

I previously posted in a different thread, however, realized that I had missed a crucial function of the formula that I need to ensure the date is computing correctly. How do I create a formula that does the following functions using the following data points?

Adding a specific number of years to a DOB, rounding up to a specific month and day, however, if there is no rounding, that the formula defaults to the DOB plus years. Additionally, I need the formula cell to remain empty if the data points for the DOB, +years, and rounding date fields are empty.

Data Points:

Sheet 1, Cell D9 =DOB
Sheet 1, Cell D42= rounding date (example 6/1)
Sheet 2, Cell B18= years added
Sheet 2, Cell F18=where formula is located

Additionally, I need to be able to replicate the formula in cells F19-F27, where it will change the sheet 2, cell B18 data point as cells B19-B27 having different "years added".

Thanks in advance!

 

[AlanSidman]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

 

[GraH - Guido]

using the formula of John in the post you refer to, it might be
=IF(OR(B2<>"",C2<>""),DATE(YEAR(A2)+B2+(TEXT(A2,"mmdd")>TEXT(C2,"mmdd")),MONTH(C2),DAY(C2)),EDATE(A2,B2*12))

 

[GraH - Guido]

Sorry, I meant this one
=IF(C2<>"",DATE(YEAR(A2)+B2+(TEXT(A2,"mmdd")>TEXT(C2,"mmdd")),MONTH(C2),DAY(C2)),EDATE(A2,B2*12))

 

[Excel Burnout]

Here is an example workbook..

Fields Used in Calculation:
-Details Sheet Cell D9
-Details Sheet Cell D42
-Calculation Sheet Cell A18

Parameters of formula-

Need to add Details Sheet Cell D9 to Calculation Sheet Cell A18, however if there is data in Details Sheet Cell D42, I need to round to the next date based on D42. I need the formula to still work if Details Sheet Cell D42 is empty (Would placing a “0” work as well, to make it less complex?), and I need no information to populate in Calculation Sheet Cell F18, if Cells Details Sheet D9, Details Sheet D42 and Calculation Sheet Cell A18 are empty.

 

[Excel Burnout]

Sorry, I meant this one
=IF(C2<>"",DATE(YEAR(A2)+B2+(TEXT(A2,"mmdd")>TEXT(C2,"mmdd")),MONTH(C2),DAY(C2)),EDATE(A2,B2*12))

Thank you. I tried both formula's and neither worked in keeping the cell empty until the data was entered.

 

[GraH - Guido]

You can add an extra IF() that evaluates if date of birth or duration in years is filled or not.
Not sure how you want this to work given the lay-out of your workbook.