## Dates

Excel ยป Dates ยป

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 count 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 [โฆ]

Find date given day and week

La Thăng asks: I want to find day if given date and week, for example : if given Tuesday, 32th [โฆ]

Count overlapping days in multiple date ranges, part 2

In the previous post I explained how to count overlapping dates between a single date range and multiple date ranges. In [โฆ]

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 missing dates in a set of date ranges

The formula in cell B8, shown above, extracts dates not included in the date ranges, in other words, dates that [โฆ]

Working with overlapping date ranges

Today's blog post is about date ranges, the techniques demonstrated here can also be applied to time or other numerical [โฆ]

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 may be dates, [โฆ]

Identify rows of overlapping records

cwrbelis asks: Hi Oscar, Great website! Keep up the good work. I have a question as to how to expand [โฆ]

Calculate min and max date among overlapping date ranges and based on a condition

Liz asks: I want to identify the overlap based on a criteria but now I want to know what is [โฆ]

Highlight date ranges overlapping selected record [VBA]

The following example shows you how to highlight overlapping ranges. How it works Select a date in the table. Conditional [โฆ]

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 column D and E. Array [โฆ]

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

The Excel defined table contains start and end dates for each date range in cell range A3:B10. Cell B13 is [โฆ]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in [โฆ]

List all unique events in a month

Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to [โฆ]

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

Table of contents Match a date when a date range is entered in a single cell Match a date when [โฆ]

Find latest date based on a condition

Most frequent value between two dates

In this article I will 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 [โฆ]

Extract unique distinct values based on a date range

Question: I have a large list of dates and other adjacent values. I want to create a distinct list from [โฆ]

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 [โฆ]