## Archive for 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 […]

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

NC asks: Thanks a tonne, Oscar. It took me about 8 hours to work through this formula piece by piece, […]

Calculate time between time zones

The worksheet below lets you enter cities and their time difference. Excel calculates the corresponding local times in E5:E8. Press F9 to […]

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 […]

Use MEDIAN function to calculate overlapping ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]

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 consecutive dates in a cell range

Column B contains random dates. The array formula in column D returns consecutive dates from column B. Array formula in […]

Days contained in a range that overlap another range

Rene asks: Hi Oscar, I need a fomula that gives me the number of days contained in a range that […]

Highlight events overlapping federal holidays

Bryan asks: i am trying to do a conditional formatting for a calendar row. (essentially, a gant chart) i have […]

Prevent users from entering overlapping date and time ranges

The picture below shows a table with data validation applied. If a user tries to enter an overlapping date range, […]

Elizabeth asks: Hi Oscar, Need help with a formula Please. I need to figure out the date range of a […]

Haroun asks: The problem is with regards to analysing the remaining life of my inventory based on the quantity available […]

Search a table and use the returning value to search another table

Nena asks: Hi Oscar,I've been trying to find the solution for my lookup problem for a while now and you […]

Date ranges: Weeks within a month

Anees asks: Hi, The above formula in A9 "Increasing date in a column" works pretty good however I have a […]

Count a specific weekday in a date range

NETWORKDAYS function returns the number of whole workdays between two dates. The array formula in this blog post counts for […]

Calculate dates in each step in a project 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 […]

How to create random numbers, text strings, dates and time values

In excel the RAND() function returns a number greater than or equal to 0 (zero) and less than 1. Combining […]

Filter weeks from a date range

Deeks asks: need to truncate the undermentioned column to create columns of weeks it has, for eg. 1/26/2011 was a […]

How many of a specific weekday falls between a start date and an end date 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 between date and time criteria

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 in excel 2007

Introduction This blog article describes how to extract coinciding date ranges. Example, Array formula Cell range B3:C25 contains example date […]

Count cells based on a condition and month

Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]

Create random dates, Mon to Fri, within a year in excel

H.G asks: I need to create lots of random dates within a certain year, for which I simply use =randbetween(). […]

Select value based on time and date [VBA]

Introduction This blog post describes how to automatically select a value based on time and date using vba in excel. […]

Generate list of random dates with criterion

Overview Today we are going to build an array formula. Step by step creating random dates with a criterion. The criterion […]

Remove duplicates and sort dates by each row in excel

I might have missunderstood Aamers question: I have a sheet with 3000 rows of invoice dates that are out of […]

Highlight duplicate values and overlapping dates in excel

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Count records between two dates with multiple parameters

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

Question: Sam asks: Is there a formula that can count blocks For eg in your picture (see picture above) if […]

Table of Contents Count all overlapping days in any number of date ranges Count overlapping days in a date range […]

Filter overlapping dates from date ranges in excel

Array formula in A2: =SMALL(IF(FREQUENCY(IF((MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1<=End)+(MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1>=Start)>1, MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1, ""), MIN(Start)+COLUMN(OFFSET($A$1, […]

Convert date ranges into dates

In a previous post I created a formula to convert dates into date ranges. Now it is time to create […]

Convert dates into date ranges

Question: How do I convert a list of dates into date ranges? Answer: Create date ranges from a list of […]

List dates outside specified date ranges in excel

I don´t think I have explained how to filter dates outside date ranges. Example, Table 1 contains start and end […]

Visualize date ranges in a calendar

Here is a picture of a simple calendar. I have used conditional formatting to: highlight date ranges (green) highlight possible […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Identify overlapping date ranges

Table of Contents Find overlapping date ranges Find overlapping date ranges with criterion Find overlapping date ranges Formula in cell […]

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

Highlight duplicates on the same date Conditional formatting formula: =SUMPRODUCT(--($C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3))>1 Highlight duplicates on same week Conditional formatting formula: =SUMPRODUCT(--($B16&"-"&YEAR($C16)&"-"&$D16=$B16:$B$16&"-"&YEAR($C16:$C$16)&"-"&$D16:$D$16))>1 Highlight […]

Filter duplicates within same date, week or month in excel

Filter duplicates on same date Array formula in F3: =INDEX($C$3:$C$11, SMALL(IF(MATCH($C$3:$C$11&$D$3:$D$11, $C$3:$C$11&$D$3:$D$11, 0)<>ROW($C$3:$C$11)-MIN(ROW($C$3:$C$11))+1, ROW($C$3:$C$11)-MIN(ROW($C$3:$C$11))+1, ""), ROW(A1))) + CTRL + SHIFT […]

Remove duplicates within same month or year

Table of contents Remove duplicates within same month and year in excel Remove duplicates within same year in excel Remove […]

Remove duplicates in same week in excel

In a previous post we created a unique distinct list of dates and data removing any duplicates on same date. […]

Remove duplicates on same date in excel

Question: Column A1 Has dates Column B as data A1 : 1/1/2010 : 5000 A2 : 2/1/2010 : 4000 A3 […]

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 […]

Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Color even months Conditional formatting formula: =NOT(MOD(MONTH($B6),2)) Color odd years Conditional formatting formula: […]

How to calculate missing months in a given date range

Question: I have dates in a list. I would like to know how to identify missing months in this list […]

Count date records between two dates in a range

In a previous article Count records between two dates in excel I counted dates that were between two dates in […]

Most common value between two dates in a range in excel

Array formula in D17: =INDEX(C3:G12, MAX(1*(IF(MAX(IF((B3:F12<=$D$15)*(B3:F12>=$D$14), COUNTIF(C3:G12, C3:G12), 0))=COUNTIF(C3:G12, C3:G12), (ROW(C3:G12)-MIN(ROW(C3:G12))+1), 0))), (MAX(IF(MAX(IF((B3:F12<=$D$15)*(B3:F12>=$D$14), COUNTIF(C3:G12, C3:G12), 0))=COUNTIF(C3:G12, C3:G12), (ROW(C3:G12)-MIN(ROW(C3:G12))+1)+((COLUMN(C3:G12)-MIN(COLUMN(C3:G12))+1)/16384), 0))-MAX(1*(IF(MAX(IF((B3:F12<=$D$15)*(B3:F12>=$D$14), COUNTIF(C3:G12, […]

Most common value between two dates in excel

In this article I will show you how to extract the most frequent value (text or number) between two dates […]

Count unique distinct values based on date criteria in a range

Question: How do I count unique values (not date values) in a range where every second column contains dates? I […]

Largest value in a range using date criteria in excel

Question: I have a range of dates and values. See picture below. I would like to identify the largest value […]

Lookup min max values within a date range

This post demonstrates how to find minimum and maximum value using two conditions. In this case they are date conditions […]

Lookup two index columns using min max values and a date range as criteria

Array formula in B19: =INDEX(Product, SMALL(IF(($C$11<=Price)*($C$12>=Price)*(OrderDate<=$C$16)*(OrderDate>=$C$15), ROW(Product)-MIN(ROW(Product))+1), ROW(1:1))) + CTRL + SHIFT + ENTER copied down as far as needed. […]

Lookup two index columns in excel

Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]

Extract dates and adjacent value in a range using a date critera in excel

The formulas below extract all dates where the year is 2009 and adjacent values in range B2:G33. Array formula in […]

Create a list of dates with blanks between quarters in excel

Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]

Count unique records between two dates

This blog article is one out of four articles on the same subject. Most common value between two dates in […]

How to highlight MAX and MIN value based on month

Conditional formatting formula to highlight max value in every month: =B2=MAX(IF(MONTH(A2)=MONTH(Date_rng), Close, "")) Conditional formatting formula to highlight min value […]

List names whos date has past in excel

Question: I have a column "A" with a last name.. I have another columb with a date in it "E"... […]

Calculate frequency the past 90 days

Question: I have two columns of data... A1-A10 contain a list of dates. B1-B10 contain a value, AA, BB, CC, […]

Filter duplicate rows and sort by date using array formula in excel

Question: How do I filter duplicate rows and sort by date? Answer: Column A and B are the original list. […]

Extract dates using a drop down list in excel

In a previous article I came up with a solution on how to extract distinct unique year-month list from a […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Create a unique distinct list from a date range

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

How to sort cells filtered by two dates

Question: I have a list containg dates and values. How do I sort values between two specific dates? Answer: Yellow […]

Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]

How to automatically calculate a specific day of a month

Question: How to calculate every third monday of a month? Answer: Array formula: =SMALL(IF(WEEKDAY(A2+ROW($1:$31)-1)=2,A2+ROW($1:$31)-1,""),3) How to create an array formula […]