using running dates in Excell to convert to text

%3CLINGO-SUB%20id%3D%22lingo-sub-1121824%22%20slang%3D%22en-US%22%3Eusing%20running%20dates%20in%20Excell%20to%20convert%20to%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1121824%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day.%20I%20have%20the%20following%20Excel%20tables%20that%20with%20formulas%20must%20convert%20to%20number%20and%20text.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20cycle%20is%20yearly.%20Thus%20said%2C%20person%20starts%20on%20a%20specific%20date.%20He%20%2F%20She%20is%20then%20shown%20as%20a%201st%20year.%20One%20year%20later%20that%20person%20on%20his%20anniversary%20date%20is%20shown%20as%20a%202nd%20year%20and%20so%20forth.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20there%20someone%20that%20can%20help%20with%20the%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1121824%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1122154%22%20slang%3D%22en-US%22%3ERe%3A%20using%20running%20dates%20in%20Excell%20to%20convert%20to%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1122154%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20simplify%20to%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFS(TODAY()%26gt%3B%3DEDATE(A2%2C24)%2C%223rd%22%2CTODAY()%26gt%3B%3DEDATE(A2%2C12)%2C%222nd%22%2CTRUE%2C%221st%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1121910%22%20slang%3D%22en-US%22%3ERe%3A%20using%20running%20dates%20in%20Excell%20to%20convert%20to%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1121910%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F528063%22%20target%3D%22_blank%22%3E%40Gerhard100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20could%20be%20this%20not%20very%20elegant%20(but%20working)%20formula%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFS(TODAY()%26gt%3B%3DDATE(YEAR(A2)%2B2%2CMONTH(A2)%2CDAY(A2))%2C%223rd%22%2CTODAY()%26gt%3B%3DDATE(YEAR(A2)%2B1%2CMONTH(A2)%2CDAY(A2))%2C%222nd%22%2CNOT(TODAY()%26gt%3B%3DDATE(YEAR(A2)%2B1%2CMONTH(A2)%2CDAY(A2)))%2C%221st%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThere%20are%20probably%20neater%20ways%20to%20achieve%20the%20same%2C%20but%20this%20all%20I%20could%20think%20of%20right%20now.%26nbsp%3BAttached%20your%20workbook%20with%20the%20formula%20in%20it.%20See%20if%20this%20what%20you%20had%20in%20mind.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

3 Replies

@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. 

@Riny_van_Eekelen 

I'd simplify to

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

@Sergei Baklan Indeed! And more elegant.