Nov 05 2021 09:49 AM - edited Nov 05 2021 09:50 AM
Hi All,
I'm trying to figure out a way in which comparison of a specific date must return a certain text based on defined parameters such as a person has certain date as 7/1/2021 (US date format) and until completion of his/her first year it must show as "current" the next year must show it as "Previous" and the following one as "Prior".
Tried with below formula but does not help much
=IF(AND((E4<=(E4+365)),NUMBERVALUE(MONTH(E4+365)&"/"&YEAR(E4+365))<=NUMBERVALUE(MONTH(TODAY())&"/"&YEAR(TODAY()))),"Current",IF(AND((E4<(E4+730)),NUMBERVALUE(MONTH(E4+730)&"/"&YEAR(E4+730))=NUMBERVALUE(MONTH(TODAY())&"/"&YEAR(TODAY()))),"Previous",IF(AND((E4<(E4+1095)),NUMBERVALUE(MONTH(E4+1095)&"/"&YEAR(E4+1095))<=NUMBERVALUE(MONTH(TODAY())&"/"&YEAR(TODAY()))),"Prior","No")))...
Any insight is much appreciated.
Thank you.
Nov 05 2021 10:13 AM
Nov 05 2021 10:34 AM
Hi Juliano, appreciate your response. Attached is the sample data. So, my intent is to have the column F % auto-populate in column D based on the formula in column C corresponding to column B & system date.
Please feel free to reach out for any additional information.
Appecriate your time & expertise.
Thank you.
Nov 05 2021 11:33 AM
Solution
I must say, I don't see the connection between your first question and the data shown in the sheet you attached in your second post!!
For starters, there's nothing there that remotely resembles the desire to show "Current," "Previous," or "Prior" based on the date shown.
So the attached file and formula below refer to your "presenting question."
This uses the new function LET, which does require the most current version of Excel in order to operate, and it presumes you're talking one full (or two full, etc) years after the date posted in E4.
Here's the formula:
=LET(
endyr,EOMONTH(E4,12),
endy2,EOMONTH(E4,24),
endy3,EOMONTH(E4,36),
IFS(
TODAY()<endyr,"Current",
TODAY()<endy2,"Previous",
TODAY()<endy3,"Prior",
TODAY()>=endy3,"Prior")
)
What LET does is enable you to name a value (or set of values) that then get used in a formula, with the formula itself being a bit shorter and easier to read as a result.
So endyr is the end of the month a year after the start date, endy2 is two years later, endy3, three.
And the IFS function picks "Current" so long as TODAY() returns a value less than the end of the month a year after start date, and so forth. IFS stops considering conditions once it encounters the first that is met.
That's how I interpreted what you wanted to do with your first post. It's possible I misinterpreted. But you can still see how such a formula could work. It can be tweaked, of course, if you intended something else.
Nov 05 2021 12:27 PM
As variant
=IFNA(
LOOKUP( INT( YEARFRAC(B2,TODAY(),1) ),
{0,1,2,3,4},
{"Current","First Anniversary Gift","Second Anniversary Gift","Third Anniversary Gift"} ),
"")
and
=IFNA(
LOOKUP( INT( YEARFRAC(B2,TODAY(),1) ),
{0,1,2,3,4},
{0,0.15,0.1,0.05} ),
0 ) * D2
Nov 05 2021 11:33 AM
Solution
I must say, I don't see the connection between your first question and the data shown in the sheet you attached in your second post!!
For starters, there's nothing there that remotely resembles the desire to show "Current," "Previous," or "Prior" based on the date shown.
So the attached file and formula below refer to your "presenting question."
This uses the new function LET, which does require the most current version of Excel in order to operate, and it presumes you're talking one full (or two full, etc) years after the date posted in E4.
Here's the formula:
=LET(
endyr,EOMONTH(E4,12),
endy2,EOMONTH(E4,24),
endy3,EOMONTH(E4,36),
IFS(
TODAY()<endyr,"Current",
TODAY()<endy2,"Previous",
TODAY()<endy3,"Prior",
TODAY()>=endy3,"Prior")
)
What LET does is enable you to name a value (or set of values) that then get used in a formula, with the formula itself being a bit shorter and easier to read as a result.
So endyr is the end of the month a year after the start date, endy2 is two years later, endy3, three.
And the IFS function picks "Current" so long as TODAY() returns a value less than the end of the month a year after start date, and so forth. IFS stops considering conditions once it encounters the first that is met.
That's how I interpreted what you wanted to do with your first post. It's possible I misinterpreted. But you can still see how such a formula could work. It can be tweaked, of course, if you intended something else.