Need help with TEXT formula returning valid dates

[leopardhawk]

Hello forum friends, I am trying to come up with a formula but I feel like I’m spinning my wheels just trying to get it started. What I need is a formula that will return a line of TEXT with two dates embedded in it. I would like the returned text to look like this:

Between September 23, 2018 and September 23, 2023

The two dates would be derived from another cell (E11) where the user had entered their birth date, in this case, September 23, 1958. The criteria would be that the dates returned would be the time period from when the user turned 60 years of age for the first date and 65 years of age for the second date.

And, if cell E11 is blank, the formula would return ‘Between mm/dd/yyyy and mm/dd/yyyy’ but without the quotes.

Any ideas much appreciated.

Thanks!

 

[Rick Rothstein]

You need to use the TEXT function to format the dates as dates...

="Between "&TEXT($E$11,"mmmm d, yyyy")&" and "&TEXT(F11,"mmmm d, yyyy")

Note: You did not give a cell reference for the second date, so I used cell F11 for it.

 

[DanteAmor]

How about:

="Between " & IF(E11="","mm/dd/yyyy and mm/dd/yyyy",TEXT(EDATE(E11,12*60),"mmmm d, yyyy")&" and "&TEXT(EDATE(E11,12*65),"mmmm d, yyyy"))

 

[leopardhawk]

Many thanks to Rick and Dante, it appears that Dante's formula will be the one that works best for my purposes. Thanks again!

 

[DanteAmor]

Dante's formula will be the one that works best for my purposes. Thanks again!

I'm glad to help you. Thanks for the feedback.