If you have ever tried to include a date inside a CONCATENATE formula in Excel, you have probably run into this frustrating result: instead of seeing "Report date: 15/01/2025" you get "Report date: 45672". That ugly number is Excel's internal date serial number, and it appears because CONCATENATE does not know you want a date — it just sees a number.
Here is how to fix it properly.
Why it happens
Excel stores every date as a plain number internally — 1st January 1900 is 1, and every day after that adds one. When you concatenate a date cell, Excel grabs the raw number behind the scenes rather than the formatted text you see on screen. The cell formatting you applied (dd/mm/yyyy, mmmm yyyy, etc.) is purely visual and gets ignored by CONCATENATE.
The fix — wrap the date in TEXT()
The solution is to convert the date to a text string first using the TEXT() function, which lets you specify exactly how the date should appear.
The syntax is:
=CONCATENATE("Report date: ", TEXT(A1, "dd/mm/yyyy"))
Or using the modern ampersand method, which does exactly the same thing:
="Report date: " & TEXT(A1, "dd/mm/yyyy")
Where A1 is the cell containing your date and "dd/mm/yyyy" is the format you want to display.
Useful format codes to use inside TEXT()
- "dd/mm/yyyy" → 15/01/2025
- "d mmmm yyyy" → 15 January 2025
- "mmmm yyyy" → January 2025
- "dd-mmm-yy" → 15-Jan-25
- "dddd, d mmmm yyyy" → Wednesday, 15 January 2025
The format code works exactly like a custom cell format in Excel — anything you can type in Format Cells → Custom, you can use inside TEXT().
A practical example
Say you are generating a dynamic report header that combines a project name from cell B1 and a report date from cell C1. Your formula would be:
="Project: " & B1 & " — Report date: " & TEXT(C1, "d mmmm yyyy")
This produces something like: Project: Tower Block A — Report date: 15 January 2025
Clean, readable, and fully dynamic — change the date in C1 and the header updates automatically.
What about TODAY()?
The same trick works perfectly when using TODAY() directly inside the formula rather than referencing a cell:
="Generated on " & TEXT(TODAY(), "d mmmm yyyy")
This is useful for automatic timestamps on reports or dashboards that always show the current date formatted nicely.
Do you use CONCATENATE and TEXT together in your Excel workflows? If you have found any other date formatting tricks worth sharing, leave them in the comments below.