Need formula dates

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.
 
R

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.
 
J

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
 
R

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

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

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.
 
S

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.
 
R

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(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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top