Forum Discussion

Gerhard100's avatar
Gerhard100
Copper Contributor
Jan 22, 2020

using running dates in Excell to convert to text

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 y...
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jan 22, 2020

Gerhard100 

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. 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 22, 2020

Riny_van_Eekelen 

I'd simplify to

=IFS(TODAY()>=EDATE(A2,24),"3rd",TODAY()>=EDATE(A2,12),"2nd",TRUE,"1st")