'Dates' category


Subcategories

  • Overlapping (17)

  • 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 […]
    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 […]
    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 […]
    Calculate date given weekday and week number
    This article demonstrates a formula that returns a date based on a week number and a weekday like Sun to […]
    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 […]
    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. […]
    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 […]
    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 […]
    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 […]
    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 […]
    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 […]
    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 […]
    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. […]
    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, […]
    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 […]
    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 […]
    Find date range based on a date
    This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]
    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 […]
    Count a specific weekday in a date range
    NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
    Schedule project dates based on a finish date
    Danielle asks: I have a schedule that I am working with and based on one date (ie. 6/4/12) different processes […]
    Extract week ranges based on a given date range
    The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3. […]
    Count weekday within date range except holidays
    Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]
    Count entries based on date and time
    Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]
    Filter overlapping date ranges
    This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
    Highlight records based on overlapping date ranges and a condition
    adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
    Count groups in calendar
    Question: Sam asks: Is there a formula that can count blocks For eg in your picture (see picture above) if […]
    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 […]
    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 […]
    Convert dates into date ranges
    The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
    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 […]
    Identify overlapping date ranges
    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 […]
    Highlight duplicates with same date, week or month
    The image above demonstrates a conditional formatting formula that highlights duplicate¬†items based on date. The first instance is not highlighted, […]
    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. […]
    Extract unique distinct year and months from dates
    Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
    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 […]
    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 […]
    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 […]
    Lookup min max values within a date range
    This article explains how to find the smallest and largest value using two conditions. In this case they are date […]
    Create a list of dates with blanks between quarters
    Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]
    Filter duplicate values and sort by corresponding date
    Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]
    Create a date range [Formula]
    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
    The image above demonstrates¬†an array formula in cell E4 that searches for the closest date in column A to the […]
    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 […]
    Calculate machine utilization
    Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]
    How to calculate a date based on specific weekday in a month
    Question: How to calculate the date of the third Monday of a given month? Answer: Column B contains dates of […]

    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

    Excel categories