Excel Date Range (Learn and Apply)

Want to know about a date range in Excel? Then, this is the right place for you. In this article, you will find ways to create date ranges, different examples of date ranges, and learn how to apply different Excel functions in a date range. You will also learn to filter, sort and look up data from date ranges using formulas.

The date range in Excel can be used for various fields, including data analysis, financial management, project management, sales and marketing, HR, inventory management, planning, and personal time management.

Download Practice Workbook

How to Create Date Range in Excel

• Here, we have a dataset containing the start and end dates of some events in Excel.

• We can show the date range for each event in a single cell using the TEXT function. To do that we used the following formula.

Date Range in Excel: 6 Ideal Examples

1. Use IF and TEXT Functions to Create Date Range When Start or End Date Is Missing

• In the above dataset, if the start date or end date is missing, you can create a date range using the IF function along with the TEXT function.
• We used the following formula to return only the start date if the end date is missing and return an empty string when the start date is missing.

2. Date Range Starting from Today and Adding Number

• Suppose we want to make a dataset where the first Project starts today. Each project takes 2 months to finish and starts 2 days after the previous project has finished.
• Using the TODAY function, you can insert the current date in your Excel worksheet. Use the following formula to do that.

• Suppose we want the end date of Project A to be after 2 months or 60 days of the start date. In that case, we will simply add 60 to Cell C5 to get the value.

• Next, to get the start date of Project B, add 2 with Cell D5.

• Now, if you autofill the rest of the cells using the Fill Handle tool, you will get a dataset like below.

3. Create a Date Sequence in Excel

• Suppose you want to create a date sequence having a 3-day interval between them.
• Insert the first 2 dates and select them.
• Then, drag down the Fill Handle tool to copy the pattern.

• Clicking on the square box, you will get other options to fill your dataset.

4. Count Values in Date Range

• To count values in a data range, you can use the COUNT function in Excel. Here is the formula to do that.

5. Highlight Values in Date Range

• Now, we have a dataset containing the Shipping Date of some products. However, we want to highlight only the cells between a certain start and end date.

• To do that, select the values of the Shipping Date column >> go to the Home tab >> click on Conditional Formatting >> select New Rule.

• In the New Formatting Rule box select Use a formula to determine which cells to format option >> Insert the following formula in the box >> click on Format.

• Choose your desired formatting and click OK. Thus, you can highlight values in a date range.

6. Get Range from Dates in Excel

• To get the minimum value from a date range you can use the MIN function in Excel.

• To get the maximum value from a date range you can use the MAX function in Excel.

How to Use IF Formula for Date Range in Excel

To return values depending on different conditions from a date range in Excel, you can use the IF function along with other Excel functions.

1. Use IF Function Only to Create IF Formula for Date Range

• Suppose we have a list of dates that represents the actual dates of shipments. If the values in Column C consist of those in that list, we want to return Shipped in Column D, otherwise, it will return an empty string.
• To do that, we used the following formula using the nested IF function.

2. Apply AND & IF Functions for Date Range in Excel

• If you want to check if a date range fulfills two conditions at the same time, you can use the IF and AND functions in Excel.
• Here, we used the following formula to return Shipped if the date is in between a range. Otherwise, it will return Pending.

3. Date Range with OR & IF Functions

• Using the OR and IF functions in Excel you can check if a date belongs to a certain date range. To do that, use the following formula.

4. Use IF and COUNTIF Functions for Date Range in Excel

• You can also use the IF and COUNTIF functions to do the same task.

5. Apply IF and TODAY Functions for Date Range in Excel

• To compare a date range with the current date, you can use the TODAY and IF functions in Excel.
• We used the following formula, to determine the status of orders based on the value in Column C compared to the current date.

6. Check Date Range Order Using IF and SORT Functions

• To check if a date range is in a certain order or not you can use the IF and SORT function in Excel.
• Use the following formula to do that. It will return Yes if the date range is sorted and No if it is not.

Read More: How to Use IF Formula for Date Range in Excel

How to Use SUMIFS with Date Range in Excel

Now, we will show you how you can sum values if it is within a certain date range. To do that we’ll use the SUMIFS function.

1. SUMIFS Between Two Dates in Excel

• Here, we have a dataset containing some products’ prices and shipping dates. We want to sum up the prices of the products which have a shipping date within a certain date range.
• To do that, we used the following formula.

2. Sum Within Dynamic Range Based on Today’s Date

• To sum values based on a date range that starts from today and extends 10 days after that, you can use the TODAY function within the SUMIFS formula to set the criteria.

3. Sum If Between Two Dates and Another Criterion

• To calculate the sum of prices for products with a shipping date between two given dates and containing the word Black in the product name, we will use the following formula.

[Fixed!] Excel SUMIFS Between Dates Not Working

When the SUMIFS function between dates is not working or returns wrong values in Excel, you can solve it using the solutions we have provided below.

• Check the format of dates and numbers: If your result using the SUMIFS function is zero, check if the dates are in text format which seems like a date.
• Use correct syntax for criteria: If you are inserting a date value insert it inside the quotation marks (“>=10/1/2023”) and insert cell references or functions outside quotation marks ( “>=”&C5 or “<“&TODAY())
• Verify formula logic: Make sure you have inserted the right logic into the formula.
• Make sure all ranges are the same size: In the formula, the sum_range and criteria_range should be equal, otherwise it will return #Value! Error. To avoid this, make sure to have the same size ranges.

Things to Remember

Make sure to convert the dates into Date format rather than Text format which looks like dates.

Frequently Asked Questions

1. What do you mean by date range in Excel?

Date ranges in Excel can convert values to text with a specified format, calculate the duration between two given dates, and set up date ranges by formatting cells as ‘date’ and using the addition (+) or subtraction (-) operations with the end date or desired duration.

2. How to calculate the difference between the two dates?

To calculate the differences between two dates you can use general operations such as “+” or “-”. For example: =(C5-B5).

Excel Date Range: Knowledge Hub

• How to Pull Data from a Date Range in Excel
• How to Calculate Average If within Date Range in Excel
• How to Find Max Date in Range with Criteria in Excel
• VLOOKUP Date Range and Return Value in Excel
• How to Use Formula for Past Due Date in Excel
• How to Calculate Due Date with Formula in Excel

Conclusion

In this article, you will have a thorough understanding of date range in Excel. It covers various topics such as creating date ranges, practical examples, using Excel functions, filtering, sorting, data lookup using formulas, using Excel features, and even using VBA for date range operations. Hope you find this helpful and informative. Feel free to comment if something seems difficult to understand. Thank you!

<< Go Back to Date-Time in Excel | Learn Excel