Excel Formula: Combine Date and Text into One Cell
As above screenshot shown, to combine date and text together, formulas can help you in Excel.
If you want to follow along with this tutorial, please download the example spreadsheet.
Generic formula
or
CONCATENATE(text," ", TEXT(date,date_format))
Syntaxt and arguments
| Text: the cell refers to the text or a text string enclosed with double quotation marks that you want to combine with date. |
| Date: a cell refers to the date or a date enclosed with double quotation marks that you want to combine with text. |
| Date_format: the date formatting that you want the date displayed, it needs to be enclosed with double quotation marks. |
Date format list
| Date_format | Date displayed as (1/2/2021) |
| d | 2 |
| dd | 02 |
| ddd | Sat |
| dddd | Saturday |
| m | 1 |
| mm | 01 |
| mmm | Jan |
| mmmm. | January |
| mmmmm | J |
| yy | 21 |
| yyyy | 2021 |
| Mm/dd/yyyy | 01/02/2021 |
| m/d/y | 1/2/21 |
| Ddd,mmm d | Sat,Jan 2 |
Return value
The both of formulas return to a text string.
How this formula works
In the column A, range A3:A4, listing the text, in column B, range B3:B4, listing dates, now to combine the texts and dates into cells, please use the formula as these:
In cell C3
Directly using text and date in the formula
In cell C4
Directly using text and date in the formula
Press Enter key to get result.
Explanation
TEXT function: to convert a number to a text in a specified format.
As we known, the date is stored as number in Excel, and to convert the date to a text and display as a specified date format, TEXT function can do the favor.
CONCATENATE function: to merge two or more texts items from multiple cells into one.
T=&: to join texts together.
Extension
To combine start date and end date together to show as a duration in text format, please use below formula:
Or
Relative Formulas
- Check If Date Is Last N Month From Today
Supposing you need to check if staff’s entry date is last 6 months from today, you can use a formula combined by AND, EOMONTH and TODAY functions in Excel to quickly handle the job. - Check If Date Is Last N Day Of Today
To check if the products are due today, you can use a formula to check if the production dates are last 7 days of today. - Get quarter from given date
This tutorial provides formulas for you to get quarter from given dates in Excel. - Get same day last year or next year
In this tutorial, it provides two formulas to separately get the same date last year and next year based on the given date in Excel.
Relative Functions
- Excel DATE Function
Create date with year, month and day - Excel YEAR Function
Returns the year of date in 4-digit serial number format - Excel MONTH Function
The MONTH is used to get the month as integer number (1 to 12) from date - Excel DAY Function
DAY function gets the day as a number (1 to 31) from a date - Excel WORKDAY Function
Add workdays to the given date and returns a workday
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
