How do I change date from serial number if it combined in a cell with other numbers/text?

[codys21]

I have a formula that includes text and a date, but Excel gives me the date in its serial number format (which I expected) but I don't know how to go about changing that when it is in a cell with other characters.



Here is my formula:

=IF(X9="","",IF(X9<1,"0% vested",IF(AND(X9>1,X9<2),"25% vested on " & DATE(YEAR(B9)+1,MONTH(B9),DAY(B9)),IF(AND(X9>2,X9<3),"50% vested on " & DATE(YEAR(B9)+2,MONTH(B9),DAY(B9)),IF(X9>3,DATE(YEAR(B9)+3,MONTH(B9),DAY(B9)),"Not 100% vested")))))



and the result:

50% vested on 37817



I would like it to say "50% vested on 7/15/2003" or something of that nature.



Thanks for the help!

Cody

 

[Jonmo1]

You have to apply the text function to your dates in the concatenation
So this bit
"25% vested on " & DATE(YEAR(B9)+1,MONTH(B9),DAY(B9))

Would change to
"25% vested on " & TEXT(DATE(YEAR(B9)+1,MONTH(B9),DAY(B9)),"m/d/yyyy")

 

[codys21]

You have to apply the text function to your dates in the concatenation
So this bit
"25% vested on " & DATE(YEAR(B9)+1,MONTH(B9),DAY(B9))

Would change to
"25% vested on " & TEXT(DATE(YEAR(B9)+1,MONTH(B9),DAY(B9)),"m/d/yyyy")

Thank You! That was quite simple!

 

[Jonmo1]

You're welcome.