top of page
Excel Navigator

Use the TEXT function to format dates in concatenations in Excel

When you enter text in a cell Excel the value is hard coded, so the text is not linked to anything in your spreadsheet and if you want to change it you need to go back into the text and manually change it.


You can combine text written into different cells together, known in Excel as concatenation, by using the ampersand symbol (&) and selecting the cells, but to update the concatenation you would still need to go into the original cells and change the text.


If part of your text is a date and you update this each month, such as a title for a worksheet, then you can combine hard coded text with the TEXT function to have your date in all your headings dynamically update when you change the value in just one cell.


Download File


If you would like to follow along download the attachment below.



Concatenation


Joining cells with text in them together is known as concatenation in Excel.


You can combine values in cells using the ampersand sign (&), but also include additional text or symbols, such as spaces, question marks, exclamation marks and punctuation by including them in double quotation marks.


Concatenating Dates and Text


Excel treats all dates as serial numbers, and these can be formatted to show as dates.


Because of this if you reference a cell with a date in a concatenation without any additional formatting in the concatenation it will return the dates serial number.

To learn more about why Excel uses serial numbers for dates and how you can format dates click here.


TEXT Function


The TEXT function in Excel is used to convert a numeric value into text in a specific format. It allows you to display numbers, dates, or times in a customized way, using a chosen format.


=TEXT(value, format_text)


  • value: The number, date, or time you want to format.

  • format_text: A format code that specifies how you want to display the value.


Concatenating Dates and Text Using the TEXT Function


Using TEXT in a concatenation means that the format of the date part of the concatenation can be changed.


The date in C2 is formatted “dd/mm/yyyy” in the cell, so this format can be entered in the format_text part of the TEXT function, with C2 being entered in the value part.

The format_text can be changed in the same way that a cell would be formatted with a date in it, so different formats entered in TEXT would produce a different outcome in the concatenation.

As the concatenation is linked to the value in cell C2 changing the value in C2 immediately changes the output of the concatenation, below the date has been changed from 31/01/2025 to 01/02/2025 in C2 and the concatenation has updated to reflect this.


Use TEXT to Dynamically Change Dates Across Worksheets


Because the TEXT function allows you to refer to a single cell which can be updated to change a concatenated texts output, you can refer concatenations across multiple worksheets to a single date so that changing a date once would update all of your texts.


By doing this with worksheet titles you can automate changing headings that refer to the current month without having to go through them one by one and manually changing them.


One way of doing this would be to have a Criteria worksheet which contains dates, and then concatenated headings in other worksheets which referred to the dates in the Criteria worksheet.


An example below shows how this could be done if you had a file which had one worksheet showing a profit and loss for one month and another showing profit and loss for year to date, and you wanted to be able to update the headings in both worksheets to show the most recent month by only changing one date on the Criteria worksheet.


  • Criteria Worksheet - A worksheet named 'Criteria' can be created that contains a start and end month which all headings in other worksheets will refer back to.


  • Current Month Worksheet – The heading for the worksheet showing the current month would show “Profit and Loss Statement – Jan-25” with the month linked to the current month in the Criteria worksheet.

  • Year to Date Worksheet – The heading for the worksheet showing the year to date would show “Profit and Loss Statement – Jan-25 to Mar-25” with the months linked to the start month and current month in the Criteria worksheet.

By linking the header of each worksheet to the Criteria worksheet the month in both headers would update when the dates in the Criteria worksheet is changed.


Conclusion


Having a date in a cell linked to a TEXT function in a concatenation allows you to update a text output by adjusting the date value in a cell.


Using the TEXT function in a concatenation also allows you to choose what format the date will show as in the output of your concatenated text.


If you have lots of concatenations across multiple worksheets that refer to a date you can link all of these concatenations to a single cell with a date so that when the date is changed the text output of all your concatenations will change too.

0 comments

댓글


bottom of page