date formatting returning a number

[stevehwc]

Hi guys

Im using this formula
="Provisional Installation date = "&B5&CHAR(10)&"Fitting Team = "

b5 has the date in it

but it is returning the following answer
Provisional Installation date = 44221

how can i amend this so it shows
Provisional Installation date = 25/01/2021

thanks

[Limor_OP]

wrap your formula in text(formula,"yymmdd")

[stevehwc]

i dont follow

[Limor_OP]

so upload your excel file for visibility.

[stevehwc]

the date is in B5

Im using the above formula in cell G1
i want it to return the date and not 44221

[MrShorty]

44221 is the serial number for the date in B5. In order to display a cell value as text in another cell, you use the TEXT() function: https://support.microsoft.com/en-us/...8-93d29371225c. So your formula should look something like

="text"&TEXT(B5,"dd/mm/yyyy")&"more text"

If you are unfamiliar with the number format codes that you can use in the TEXT() function, this was one of my most commonly referenced help files when learning a spreadsheet: https://support.microsoft.com/en-us/...rs=en-us&ad=us

[stevehwc]

thank u mr shorty

[BobWGABIAB]

44221 is the serial number for the date in B5. In order to display a cell value as text in another cell, you use the TEXT() function: . So your formula should look something like

="text"&TEXT(B5,"dd/mm/yyyy")&"more text"

If you are unfamiliar with the number format codes that you can use in the TEXT() function, this was one of my most commonly referenced help files when learning a spreadsheet:

Thanks, this also helped me. I had to some tinkering, but I think I can get it to make a pivot table with dates but not including the year.