Excel Formula: Create Date Range From Two Dates
Here lists two dates, the job is to create the date range based on the two dates and connect them with “-“. Here this tutorial provides a formula with TEXT function to solve this job.
If you want to follow along with this tutorial, please download the example spreadsheet.
Generic formula:
| TEXT(date1,"date_format")&" - "&TEXT(date2,"date_format") |
Syntaxt and Arguments
| Date1, date2:the two dates you want to create the date range based on. |
| Date_format:the date formatting that you want to display in the result. |
Return Value
The formula returns to a text value.
How this formula works
For instance, two dates are in cell B3 and C3, now to create a date range, please use below formula:
| =TEXT(B3,"mm/dd")&" - "&TEXT(C3,"mm/dd") |
Press Enter key to get the date range.
Explanation
TEXT function: converts number to text with a specific format.
If you directly use the “&” in the formula to connect two dates to create a date range, the result will be displayed as 43952-43985 since the date is stored as number in Excel. Now using the TEXT function to convert the number/date to a text string in a specific format (a date format), then concatenate two dates, the result will be seen as a date range.
Relative Formulas
- Check if a date is workday
This tutorial provides a formula to to check a date if is a workday. - Check two dates are in the same year month
To check if two dates in the same year month, you can use the formula in this tutorial. - Count Days From Today
This tutorial provides the best formula to calculate the difference between a date and today in Excel. - Convert Weekday in A Date Range
If there is a list of dates, how can you count a certain day of week in such a list of dates?
Relative Functions
- Excel WEEKNUM Function
Returns the week number of the given date in a year - Excel WORKDAY Function
Adds workdays to the given start date and returns a workday - 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 NOW Function
Get the current time and date
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.
