SOLVED

Date formatting in a formula

Copper Contributor

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.

 

8 Replies
Can you post just a sample data where you show the result you expect?

@Juliano-Petrukio 

 

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.

best response confirmed by rockstar_sprt (Copper Contributor)
Solution

@rockstar_sprt 

 

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.

 

@rockstar_sprt 

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
@mathetes thank you so much. You nailed it
I apologise for the delayed reply
Thank you, will try this version as well.
1 best response

Accepted Solutions
best response confirmed by rockstar_sprt (Copper Contributor)
Solution

@rockstar_sprt 

 

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.

 

View solution in original post