How do you write a text and a date in the same cell, formatted as a date? This is a common issue with Excel because it will not be able to display your date as a formatted date.
- In cells D1 and D2, you have the same date but with 2 different formats.
- Let's link the text "From" and the cell D1 with the & sign
- The date format is not kept 😱😱😱
In this situation, the only way to keep the date format is to use the TEXT function, which helps you keep any number format inside a string.
Presentation of the TEXT function
The TEXT function has two arguments
- The number to format
It could be a value or a reference to a cell
- The format number code
It's similar to the custom code that you use with your dates or your number
=TEXT(Number, Format)
Writing of the TEXT function
- Keep your extra text between double-quote
- Add the symbol &
- Write the formula TEXT
- The first argument of the function is the value you want to keep
- The second argument is the format you want to return. The values of the code are the same as the date format codes. For instance, you can write this formula
="From "&TEXT(D1,"dd/mm/yyyy")
Or this one
="From "&TEXT(D1,"ddd, dd mmm yyyy")
Text and date in the same cell for a calendar
In the next picture, we have created a formula with 2 TEXT functions
- One for the first date
- And one for the last date.
The formula is a little bit long but not complex. If you want to learn how to create a calendar, go to this article. Or if you want to download a calendar that records your data from one month to the next one, here is the link.
