Need formula dates
 

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.

--
Sheldon

Need formula dates
 

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.

--
Rick (MVP - Excel)


"Sheldon" wrote in message
...
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.

--
Sheldon

Need formula dates
 

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})+L OOKUP(D2,{0,3,4,5,10,11},{0,2,5,0,15,0}),MONTH(A2) ,DAY(A2))


If this post helps click Yes
---------------
Jacob Skaria


"Sheldon" wrote:

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.

--
Sheldon

Need formula dates
 

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Sheldon" wrote in message
...
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.

--
Sheldon

Need formula dates
 

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

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
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})+L OOKUP(D2,{0,3,4,5,10,11},{0,2,5,0,15,0}),MONTH(A2) ,DAY(A2))


If this post helps click Yes
---------------
Jacob Skaria


"Sheldon" wrote:

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.

--
Sheldon

Need formula dates
 

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


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Sheldon" wrote in message
...
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.

--
Sheldon

Need formula dates
 

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" wrote in message
...
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


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Sheldon" wrote in message
...
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.

--
Sheldon

Need formula dates
 

Uh, No.

<g (VERY sheepish)

Thanks!
--
Sheldon


"Rick Rothstein" wrote:

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" wrote in message
...
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


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Sheldon" wrote in message
...
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.

--
Sheldon