Need formula dates

[Sheldon]

Hello

I have the following scenario:

Date ColA ColB ColC NewDate
1/3/2001 3 4 10 FORMULA CELL
2/15/2008 4 4 4 FORMULA CELL

If a number is 3 then it's equivalent to two years. If it's 4, it means 5
years and if it's 10, it is 15 years.

I need to then take the Date column and subtract ColA, ColB and ColC to give
a date in the NewDate field.

 

[Rick Rothstein]

Your example is a little confusing to me. Are you saying you have those
dates in Excel's Column A and the what you labeled as ColA, ColB and ColC
are really in Excel's Column B, C and D? If so, then I think this
array-entered** formula does what you want...

=DATE(YEAR(A1)-SUM(CHOOSE(1+MOD(B1:B3-3,5),2,5,15)),MONTH(A1),DAY(A1))

** Commit formula using Ctr+Shift+Enter, not just Enter by itself.

 

[Jacob Skaria]

With the 1st date in cell A2 try the below formula in E2

ColA ColB ColC ColD ColE
7/30/2009 3 4 10 7/30/2031

=DATE(YEAR(A2)+LOOKUP(B2,{0,3,4,5,10,11},{0,2,5,0,15,0})+LOOKUP(C2,{0,3,4,5,10,11},{0,2,5,0,15,0})+LOOKUP(D2,{0,3,4,5,10,11},{0,2,5,0,15,0}),MONTH(A2),DAY(A2))


If this post helps click Yes

 

[Rick Rothstein]

Correction... I used the wrong range in my first formula; here is the
corrected formula (still array-entered**)...

=DATE(YEAR(A1)-SUM(CHOOSE(1+MOD(B11-3,5),2,5,15)),MONTH(A1),DAY(A1))

** Commit formula using Ctr+Shift+Enter, not just Enter by itself.

 

[Rick Rothstein]

You should have minus signs in front of those LOOKUP function calls as the
OP said he wanted to subtract the coded year amounts.

 

[Sheldon]

Hi Rick -

I am getting the wrong year for your formula.

You are correct about Jacob's answer - it just needed the minus signs.

 

[Rick Rothstein]

I checked and my formula produces the same dates as Jacob's formula does...
did you adjust the row number I used in my formula to your actual starting
data row? For example, if your data started on Row 2, the second (corrected)
array-entered** formula I posted would become...

=DATE(YEAR(A2)-SUM(CHOOSE(1+MOD(B22-3,5),2,5,15)),MONTH(A2),DAY(A2))

** Commit formula using Ctr+Shift+Enter, not just Enter by itself.

--
Rick (MVP - Excel)

Hi Rick -

I am getting the wrong year for your formula.

You are correct about Jacob's answer - it just needed the minus signs.

 

[Sheldon]

Uh, No.

<g> (VERY sheepish)

Thanks!