'Dates' category


You are here: Excel » Dates »

Subcategories

  • Overlapping (17)

  • Get date ranges from a schedule
    The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based […]
    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
    The image above shows a formula in cell D3 that extract dates from column B. Column B contains dates in […]
    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
    The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]
    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 latest date based on a 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 How to enter an array formula Filter unique […]
    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


    Latest updated articles.
    More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.
    More than 1300 formulas organized in subcategories.
    Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.
    Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.
    Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.
    Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.
    Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.
    The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.
    An Excel feature that lets you visualize data in a graph.
    Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.
    Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.
    VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.
    A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.
    UDF stands for User Defined Functions and is custom built functions anyone can create.
    A list of all published articles.