Title with Text and Date in the same cell

Title with Text and Date in the same cell
Last Updated on 08/05/2024
Reading time: 2 minutes

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 a formula, the date format is lost

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

  1. The number to format

    It could be a value or a reference to a cell

  2. 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

  1. Keep your extra text between double-quote
  2. Add the symbol &
  3. Write the formula TEXT
  4. The first argument of the function is the value you want to keep
  5. 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")

The TEXT function changes the format of the dates

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.

Example title with date in a calendar with the TEXT function

Leave a Reply

Your email address will not be published. Required fields are marked *

Title with Text and Date in the same cell

Reading time: 2 minutes
Last Updated on 08/05/2024

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 a formula, the date format is lost

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

  1. The number to format

    It could be a value or a reference to a cell

  2. 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

  1. Keep your extra text between double-quote
  2. Add the symbol &
  3. Write the formula TEXT
  4. The first argument of the function is the value you want to keep
  5. 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")

The TEXT function changes the format of the dates

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.

Example title with date in a calendar with the TEXT function

Leave a Reply

Your email address will not be published. Required fields are marked *