Jan 22 2020 12:45 AM
Good day. I have the following Excel tables that with formulas must convert to number and text.
The cycle is yearly. Thus said, person starts on a specific date. He / She is then shown as a 1st year. One year later that person on his anniversary date is shown as a 2nd year and so forth.
is there someone that can help with the formula.
Jan 22 2020 01:39 AM
That could be this not very elegant (but working) formula:
=IFS(TODAY()>=DATE(YEAR(A2)+2,MONTH(A2),DAY(A2)),"3rd",TODAY()>=DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)),"2nd",NOT(TODAY()>=DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))),"1st")
There are probably neater ways to achieve the same, but this all I could think of right now. Attached your workbook with the formula in it. See if this what you had in mind.
Jan 22 2020 03:58 AM
I'd simplify to
=IFS(TODAY()>=EDATE(A2,24),"3rd",TODAY()>=EDATE(A2,12),"2nd",TRUE,"1st")
Jan 22 2020 05:47 AM
@Sergei Baklan Indeed! And more elegant.