'Dates' category
Subcategories
Get date ranges from a schedule
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]
How to sum overlapping time
The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]
The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]
Count overlapping days across multiple date ranges
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
Sum numerical ranges between two numbers
This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]
This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]
Count overlapping days in multiple date ranges, part 2
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
Count overlapping days in multiple date ranges
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
Find empty dates in a set of date ranges
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]
Working with overlapping date ranges
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]
How to calculate overlapping time ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
Identify rows of overlapping records
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
Find earliest and latest overlapping dates in a set of date ranges based on a condition
This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]
This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]
Highlight date ranges overlapping selected record [VBA]
This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]
This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]
Find all sequences of consecutive dates
This article demonstrates formulas that display dates that follow each other in sequence. Dates June 5, 2025 and June 6, […]
This article demonstrates formulas that display dates that follow each other in sequence. Dates June 5, 2025 and June 6, […]
Units contained in a range that overlap another range
This article demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles […]
This article demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles […]
Prevent overlapping date and time ranges using data validation
The picture above shows an Excel Table with Data Validation applied. An error dialog box appears if a user tries […]
The picture above shows an Excel Table with Data Validation applied. An error dialog box appears if a user tries […]
Working with date ranges
Table of Contents Find date range based on a date Sort dates within a date range 1. Find date range […]
Table of Contents Find date range based on a date Sort dates within a date range 1. Find date range […]
Create date ranges that stay within month
This article demonstrates a formula that creates date ranges based on a given number of days and the end date […]
This article demonstrates a formula that creates date ranges based on a given number of days and the end date […]
Filter overlapping date ranges
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
Extract dates from overlapping date ranges
The array formula in cell B3 extracts overlapping dates based on the date ranges in columns D and E. What's […]
The array formula in cell B3 extracts overlapping dates based on the date ranges in columns D and E. What's […]
Convert date ranges into dates
This article demonstrates how to create a list of dates based on multiple date ranges. Table of contents Convert date […]
This article demonstrates how to create a list of dates based on multiple date ranges. Table of contents Convert date […]
List dates outside specified date ranges
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
Identify overlapping date ranges
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
List all unique distinct rows in a given month
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
Filter duplicates within same date, week or month
The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]
The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]
Formula for matching a date within a date range
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
Find the most recent date that meets a particular condition
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
Most frequent value between two dates
I will in this article show you how to extract the most frequent value (text or number) between two dates […]
I will in this article show you how to extract the most frequent value (text or number) between two dates […]
How to create date ranges in Excel
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
Lookup the nearest date
Table of Contents Lookup the nearest date Lookup min max values within a date range 1. Lookup the nearest date […]
Table of Contents Lookup the nearest date Lookup min max values within a date range 1. Lookup the nearest date […]
Filter unique distinct values based on a date range
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
Calculate machine utilization
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]
Excel formula categories
AverageChooseCombine MergeCompareConcatenateConditional FormattingCountCount valuesDatesDuplicatesExtractFilterFilter recordsFiltered valuesFilterxmlHyperlinkIf cellIndex MatchLogicLookupsMatchMaxMinNumbers in sumOR logicOverlappingPartial matchRecordsSequenceSmallSort bySort valuesSumSumifsSumproductString manipulationTimeUnique distinct recordsUnique distinct valuesUnique recordsUnique valuesVlookupVlookup return values