Forum Discussion
Solved
Help needed with Date function
Hi everyone,
I need help in creating a logic, probably a date function which should return below highlighted output:
In column A, is the measure start month and column B is measure end month in (MMDDYY)format, I need a formula in cell C2 and D2 which can give me 12 months of tracking period.
The output should be the one shown in image. Here's what I tried so far:
=IFERROR(DATE(YEAR(A2),MONTH(A2),DAY(1)),"")
this formula doesn't give 12 months of tracking period though, appreciate your help!
Thanks!
Your expected values for Measure End Month do not actually match your stated "12 months" (as in 12 full months), but instead are 11 months after the Measure Start Month Tracking. (Cell D3 is off by one year, apparently a mistype.)
So if 12 months from your column C date is what you want, a simple formula for column D is:=IF( ISNUMBER(A2), DATE(YEAR(A2), MONTH(A2)+12, 1), "")
But if the latter (11 months) is what you want, then use:=IF( ISNUMBER(A2), DATE(YEAR(A2), MONTH(A2)+11, 1), "")
In both cases, the contents of column B are irrelevant.
If you should decide you want to calculate the date that is the last day of a month, you can either use 0 for the third parameter to the DATE function (with a second parameter that is one month "too large"), or use the EOMONTH function instead.
Aside: Note that your current formula should actually use 1 rather than the (misleading, but accidentally accurate) DAY(1).
- Thank you! this is also a very good alternative to achieve same results.
You are welcome. Everything in Excel could be done by few different ways.
Your expected values for Measure End Month do not actually match your stated "12 months" (as in 12 full months), but instead are 11 months after the Measure Start Month Tracking. (Cell D3 is off by one year, apparently a mistype.)
So if 12 months from your column C date is what you want, a simple formula for column D is:=IF( ISNUMBER(A2), DATE(YEAR(A2), MONTH(A2)+12, 1), "")
But if the latter (11 months) is what you want, then use:=IF( ISNUMBER(A2), DATE(YEAR(A2), MONTH(A2)+11, 1), "")
In both cases, the contents of column B are irrelevant.
If you should decide you want to calculate the date that is the last day of a month, you can either use 0 for the third parameter to the DATE function (with a second parameter that is one month "too large"), or use the EOMONTH function instead.
Aside: Note that your current formula should actually use 1 rather than the (misleading, but accidentally accurate) DAY(1).- thank you! this is exactly what I needed, appreciate your help!
