Time Left Until Maturity

[jdpro]

Hello!
A maturity date is in cell R3 9/1/2054.
I would like an Excel formula for cell V3 concatenated that will calculate the number of years, months, and days left for me, and then return "Matured" once the end date is reached. Can someone please help me with this? Thank you very much.

 

[HongRu]

Try.

=IF(R3<=TODAY(),
    "Matured",
    DATEDIF(TODAY(),R3,"Y") & "y" &
    DATEDIF(TODAY(),R3,"YM") & "m" &
    IF(DAY(R3)>=DAY(TODAY()),
       DAY(R3)-DAY(TODAY()),
       R3-DATE(YEAR(R3),MONTH(R3)-1,DAY(TODAY()))) & "d")

 

[jdpro]

That one works, thank you!

 

[HongRu]

You're welcome.
I'm glad it works.

 

[Peter_SSs]

.. in case you are interested in a shorter version
(My dates are d/m/y format)

25 02 03.xlsm
	R	S	T
2	Maturity Date	Post 2	Post 5
3	1/08/2026	1y5m29d	1y5m29d
4	15/01/2025	Matured	Matured
5	9/01/2054	28y11m6d	28y11m6d
6	4/02/2025	0y0m1d	0y0m1d
7	3/02/2032	7y0m0d	7y0m0d
ymd

Cell Formulas
Range	Formula
S3:S7	S3	=IF(R3<=TODAY(), "Matured", DATEDIF(TODAY(),R3,"Y") & "y" & DATEDIF(TODAY(),R3,"YM") & "m" & IF(DAY(R3)>=DAY(TODAY()), DAY(R3)-DAY(TODAY()), R3-DATE(YEAR(R3),MONTH(R3)-1,DAY(TODAY()))) & "d")
T3:T7	T3	=LET(t,TODAY(),x,DATEDIF(t,R3,{"Y","YM"}),IF(R3<=t,"Matured",TEXTJOIN("y",,x)&"m"&R3-EDATE(t,SUM(x*{12,1}))&"d"))

 

[jdpro]

I like it, thanks Peter!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.