| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| need a formula that has to do with dates. | Excel Worksheet Functions | |||
| Dates formula | Excel Worksheet Functions | |||
| formula to add dates. | Excel Worksheet Functions | |||
| Need a Formula for DATES | Excel Worksheet Functions | |||
| formula for dates | Excel Discussion (Misc queries) | |||
Linear Mode
