Need formula dates

  • Thread starter Thread starter Sheldon
  • Start date Start date
S

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.
 
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.
 
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
 
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(B1:D1-3,5),2,5,15)),MONTH(A1),DAY(A1))

** Commit formula using Ctr+Shift+Enter, not just Enter by itself.
 
You should have minus signs in front of those LOOKUP function calls as the
OP said he wanted to subtract the coded year amounts.
 
Hi Rick -

I am getting the wrong year for your formula.

You are correct about Jacob's answer - it just needed the minus signs.
 
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(B2:D2-3,5),2,5,15)),MONTH(A2),DAY(A2))

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

--
Rick (MVP - Excel)


Sheldon said:
Hi Rick -

I am getting the wrong year for your formula.

You are correct about Jacob's answer - it just needed the minus signs.
Click to expand...
 
Back
Top