How to write text and a date in the same cell, keeping the date format? This is a common problem in Excel. When you add text with a date, Excel can’t show the date in the correct format.
- In cells D1 and D2, you have the same date in two different formats.
- Use the text "From" and link it to cell D1 with the & symbol.
- The date format disappears 😱😱😱
To keep the date format, use the TEXT function, which allows you to keep a specific number format inside a text.
Overview of the TEXT Function
The TEXT function needs two parts:
- The number to format. It can be a value or a cell reference.
- The custom format code. This is like the code used for dates or numbers.
=TEXT(Number, Format)
How to Write the TEXT Function
- Put extra text in double quotes.
- Use the & symbol to join text and date.
- Write the TEXT formula.
- The first argument is the value you want to format.
- The second argument is the format you want. You can use date format codes. Here’s an example:
="From "&TEXT(D1,"dd/mm/yyyy")
Or you can write:
="From "&TEXT(D1,"ddd, dd mmm yyyy")
Text and Date in the Same Cell for a Calendar
In the next image, we used a formula with two TEXT functions:
- One for the first date.
- Another for the last date.
The formula is a bit long, but it’s not difficult. If you want to learn more about creating a calendar, check out this article. Or, if you want to download a calendar that saves your data from month to month, click here.
