|
If Array EDATE formula help
Hi,
I've looked in this forum and the help feature in Excel and have only partially succeeded. I'm trying to return an Warranty Expiry Date based on date of receipt of product. Cell A2 has Date 9/06/08 Cell B2 has data validation list - "1 yr", "2 yr", "3 yr", etc. Here are the two formulas I've played with: =IF(B2="1 yr",EDATE(A2,12),IF(B2="2 yr",EDATE(A2,24))) =IF(B2={"1 yr","2 yr","3 yr","4 yr"},EDATE(A2,{12,24,36,48})) they work great if I have "1 yr" selected, it calculates 12 additional months and returns 9/06/09, but returns FALSE if "2 yr" or higher is selected. I know I've missed it by just a comma or something. Any suggestions are welcome. -- Thank you, scrowley(AT)littleonline.com |
If Array EDATE formula help
Try this:
=EDATE(A2,LEFT(B2)*12) Assuming you don't project beyond 9 yrs. -- Biff Microsoft Excel MVP "SCrowley" wrote in message ... Hi, I've looked in this forum and the help feature in Excel and have only partially succeeded. I'm trying to return an Warranty Expiry Date based on date of receipt of product. Cell A2 has Date 9/06/08 Cell B2 has data validation list - "1 yr", "2 yr", "3 yr", etc. Here are the two formulas I've played with: =IF(B2="1 yr",EDATE(A2,12),IF(B2="2 yr",EDATE(A2,24))) =IF(B2={"1 yr","2 yr","3 yr","4 yr"},EDATE(A2,{12,24,36,48})) they work great if I have "1 yr" selected, it calculates 12 additional months and returns 9/06/09, but returns FALSE if "2 yr" or higher is selected. I know I've missed it by just a comma or something. Any suggestions are welcome. -- Thank you, scrowley(AT)littleonline.com |
If Array EDATE formula help
Of course, if the OP did want to go past 9 years, this modification to your
formula would allow up to 99 years... =EDATE(A2,LEFT(B2,2)*12) -- Rick (MVP - Excel) "T. Valko" wrote in message ... Try this: =EDATE(A2,LEFT(B2)*12) Assuming you don't project beyond 9 yrs. -- Biff Microsoft Excel MVP "SCrowley" wrote in message ... Hi, I've looked in this forum and the help feature in Excel and have only partially succeeded. I'm trying to return an Warranty Expiry Date based on date of receipt of product. Cell A2 has Date 9/06/08 Cell B2 has data validation list - "1 yr", "2 yr", "3 yr", etc. Here are the two formulas I've played with: =IF(B2="1 yr",EDATE(A2,12),IF(B2="2 yr",EDATE(A2,24))) =IF(B2={"1 yr","2 yr","3 yr","4 yr"},EDATE(A2,{12,24,36,48})) they work great if I have "1 yr" selected, it calculates 12 additional months and returns 9/06/09, but returns FALSE if "2 yr" or higher is selected. I know I've missed it by just a comma or something. Any suggestions are welcome. -- Thank you, scrowley(AT)littleonline.com |
If Array EDATE formula help
To shed some light on your actual problem:
=IF(B2="1 yr",EDATE(A2,12),IF(B2="2 yr",EDATE(A2,24))) returns FALSE if "2 yr" or higher is selected. That means what you *see* in B2 does not equal "2 yr". There may be unseen characters like leading/trailing spaces: <space2 yr 2 yr<space <space2 yr<space -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =EDATE(A2,LEFT(B2)*12) Assuming you don't project beyond 9 yrs. -- Biff Microsoft Excel MVP "SCrowley" wrote in message ... Hi, I've looked in this forum and the help feature in Excel and have only partially succeeded. I'm trying to return an Warranty Expiry Date based on date of receipt of product. Cell A2 has Date 9/06/08 Cell B2 has data validation list - "1 yr", "2 yr", "3 yr", etc. Here are the two formulas I've played with: =IF(B2="1 yr",EDATE(A2,12),IF(B2="2 yr",EDATE(A2,24))) =IF(B2={"1 yr","2 yr","3 yr","4 yr"},EDATE(A2,{12,24,36,48})) they work great if I have "1 yr" selected, it calculates 12 additional months and returns 9/06/09, but returns FALSE if "2 yr" or higher is selected. I know I've missed it by just a comma or something. Any suggestions are welcome. -- Thank you, scrowley(AT)littleonline.com |
If Array EDATE formula help
THANK YOU VERY MUCH!!! I love this forum!
-- Thank you, scrowley(AT)littleonline.com "T. Valko" wrote: To shed some light on your actual problem: =IF(B2="1 yr",EDATE(A2,12),IF(B2="2 yr",EDATE(A2,24))) returns FALSE if "2 yr" or higher is selected. That means what you *see* in B2 does not equal "2 yr". There may be unseen characters like leading/trailing spaces: <space2 yr 2 yr<space <space2 yr<space -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =EDATE(A2,LEFT(B2)*12) Assuming you don't project beyond 9 yrs. -- Biff Microsoft Excel MVP "SCrowley" wrote in message ... Hi, I've looked in this forum and the help feature in Excel and have only partially succeeded. I'm trying to return an Warranty Expiry Date based on date of receipt of product. Cell A2 has Date 9/06/08 Cell B2 has data validation list - "1 yr", "2 yr", "3 yr", etc. Here are the two formulas I've played with: =IF(B2="1 yr",EDATE(A2,12),IF(B2="2 yr",EDATE(A2,24))) =IF(B2={"1 yr","2 yr","3 yr","4 yr"},EDATE(A2,{12,24,36,48})) they work great if I have "1 yr" selected, it calculates 12 additional months and returns 9/06/09, but returns FALSE if "2 yr" or higher is selected. I know I've missed it by just a comma or something. Any suggestions are welcome. -- Thank you, scrowley(AT)littleonline.com |
If Array EDATE formula help
Can you break it down and tell me what each step is doing? Thank you. I'm
trying to understand formula logic so that I too may be of help to others someday. -- Thank you, scrowley(AT)littleonline.com "Rick Rothstein" wrote: Of course, if the OP did want to go past 9 years, this modification to your formula would allow up to 99 years... =EDATE(A2,LEFT(B2,2)*12) -- Rick (MVP - Excel) "T. Valko" wrote in message ... Try this: =EDATE(A2,LEFT(B2)*12) Assuming you don't project beyond 9 yrs. -- Biff Microsoft Excel MVP "SCrowley" wrote in message ... Hi, I've looked in this forum and the help feature in Excel and have only partially succeeded. I'm trying to return an Warranty Expiry Date based on date of receipt of product. Cell A2 has Date 9/06/08 Cell B2 has data validation list - "1 yr", "2 yr", "3 yr", etc. Here are the two formulas I've played with: =IF(B2="1 yr",EDATE(A2,12),IF(B2="2 yr",EDATE(A2,24))) =IF(B2={"1 yr","2 yr","3 yr","4 yr"},EDATE(A2,{12,24,36,48})) they work great if I have "1 yr" selected, it calculates 12 additional months and returns 9/06/09, but returns FALSE if "2 yr" or higher is selected. I know I've missed it by just a comma or something. Any suggestions are welcome. -- Thank you, scrowley(AT)littleonline.com |
If Array EDATE formula help
=EDATE(A2,LEFT(B2,2)*12)
My formula is taking advantage of the fact that your number is separated from your text ("yr") by a space. The LEFT function (with 2 as the second argument) will grab the leftmost two characters from the text. If that is a two-digit number, then those two digits are returned; if that is a one-digit number, then that digit with a space after it is returned. This number is next multiplied by 12 to get the number of months represented by that number of years (which is required for the EDATE function which works with months, not years). As it turns out, Excel goes out of its way to aid you in performing calculations with numbers when it can. One of those ways it helps is to ignore the trailing space when multiply a real number times a text string consisting of a digit followed by any number of spaces (it effectively performs the equivalent of applying the TRIM function to the text and then converting the text number to a real number before performing the calculation). To see this, put this example formula in a cell and you will see Excel TRIMs off the leading and trailing spaces, converts the text number to a real number and performs the math... =" 123 "*3 -- Rick (MVP - Excel) "SCrowley" wrote in message ... Can you break it down and tell me what each step is doing? Thank you. I'm trying to understand formula logic so that I too may be of help to others someday. -- Thank you, scrowley(AT)littleonline.com "Rick Rothstein" wrote: Of course, if the OP did want to go past 9 years, this modification to your formula would allow up to 99 years... =EDATE(A2,LEFT(B2,2)*12) -- Rick (MVP - Excel) "T. Valko" wrote in message ... Try this: =EDATE(A2,LEFT(B2)*12) Assuming you don't project beyond 9 yrs. -- Biff Microsoft Excel MVP "SCrowley" wrote in message ... Hi, I've looked in this forum and the help feature in Excel and have only partially succeeded. I'm trying to return an Warranty Expiry Date based on date of receipt of product. Cell A2 has Date 9/06/08 Cell B2 has data validation list - "1 yr", "2 yr", "3 yr", etc. Here are the two formulas I've played with: =IF(B2="1 yr",EDATE(A2,12),IF(B2="2 yr",EDATE(A2,24))) =IF(B2={"1 yr","2 yr","3 yr","4 yr"},EDATE(A2,{12,24,36,48})) they work great if I have "1 yr" selected, it calculates 12 additional months and returns 9/06/09, but returns FALSE if "2 yr" or higher is selected. I know I've missed it by just a comma or something. Any suggestions are welcome. -- Thank you, scrowley(AT)littleonline.com |
If Array EDATE formula help
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "SCrowley" wrote in message ... THANK YOU VERY MUCH!!! I love this forum! -- Thank you, scrowley(AT)littleonline.com "T. Valko" wrote: To shed some light on your actual problem: =IF(B2="1 yr",EDATE(A2,12),IF(B2="2 yr",EDATE(A2,24))) returns FALSE if "2 yr" or higher is selected. That means what you *see* in B2 does not equal "2 yr". There may be unseen characters like leading/trailing spaces: <space2 yr 2 yr<space <space2 yr<space -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =EDATE(A2,LEFT(B2)*12) Assuming you don't project beyond 9 yrs. -- Biff Microsoft Excel MVP "SCrowley" wrote in message ... Hi, I've looked in this forum and the help feature in Excel and have only partially succeeded. I'm trying to return an Warranty Expiry Date based on date of receipt of product. Cell A2 has Date 9/06/08 Cell B2 has data validation list - "1 yr", "2 yr", "3 yr", etc. Here are the two formulas I've played with: =IF(B2="1 yr",EDATE(A2,12),IF(B2="2 yr",EDATE(A2,24))) =IF(B2={"1 yr","2 yr","3 yr","4 yr"},EDATE(A2,{12,24,36,48})) they work great if I have "1 yr" selected, it calculates 12 additional months and returns 9/06/09, but returns FALSE if "2 yr" or higher is selected. I know I've missed it by just a comma or something. Any suggestions are welcome. -- Thank you, scrowley(AT)littleonline.com |
| All times are GMT +1. The time now is 10:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com