You can perform similar operations on dates and numbers because dates in Excel are nothing more than numbers in a date format.
You can perform addition, subtraction, and operations on ranges.
In this lesson, you are going to learn how you can work with date ranges.
Date range within a single cell
To display two dates as a date range inside a single cell, you have to use the TEXT function. It converts a value to text in the specified format.
The date in this example is written in the following format: yyyy-mm-dd.
Let’s create a date range in this format and insert this formula into cell D2:
1 |
=TEXT(A2,"yyyy-mm-dd")&" - "&TEXT(B2,"yyyy-mm-dd") |
Fill two remaining cells to get ranges for all dates.
The problem with this format is that it has dashes inside dates that don’t look good with dashes representing a range. This notation is also too wide. We have to make it narrower.
We are going to use the US date format MDY, with the year taking two places, instead of four. Let’s also change dashes into forward slashes.
1 |
=TEXT(A2,"mm/dd/yy")&" - "&TEXT(B2,"mm/dd/yy") |
This is how it looks after modifications.
Of course, you can make it even narrower if you decide to get rid of day or year from the notation.
Create a list of sequential dates
As I mentioned earlier, dates are just numbers, and you can create a list of sequential numbers. In the same way, you can create a list of sequential dates.
The simplest way to do it is to enter the starting date and drag as many positions as you want. You can do it both vertically and horizontally.
Each of these dates differs by a day because 1 equals 1 day in Excel.
There is a thing you have to remember. I said that you can create sequential dates the same way as numbers.
There is a slight difference. This table illustrates it.
Numbers | Dates | |
Drag | Copy | Create sequential values |
Ctrl + Drag | Create sequential values | Copy |
Count values in a range
One method to count days in a range is to use the COUNT function. This function counts the number of values inside a range.
I’ll show you two additional ways to count the number of days inside a date range.
The first way is this formula: end_date – start_date.
1 |
=A7 - A2 |
The last method I would like to show you uses the DAYS function. It returns the number of days between two dates, and it looks like this:
1 |
=DAYS(A7, A2) |
All of these methods return the same value, which is 5.
Highlight values in the range
If you want to highlight only dates that are in a range between two dates, you can use conditional formatting.
First, select the dates you want to check.
Go to Home >> Conditional Formatting >> New Rule.
Inside the New Formatting Window, click Use a formula to determine which cells to format and insert this formula:
1 |
=AND(A2>$C$2,A2<$D$2) |
Click the Format button, and then the Fill tab. Choose a color.
Click OK to see the result.
There are three dates inside a list that are inside this range.
Get a range from dates
The last thing to learn is how to get the first and last date and display it as a range.
The start date is the lowest number, and the end date is the highest. This can be achieved with the MIN and MAX functions.
The values in the MIN and MAX functions are formatted as dates by default, so if you didn’t apply different formatting, you don’t have to do anything else.