Date formatting in a formula

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.