## Count dates inside a date range

How do I automatically count dates in a specific date range?

**Array formula in cell D3:**

This is an array formula so make sure you press Ctrl + Shift + Enter.

**Alternative array formula in cell D3:**

**If you want to count the start date and end date also, try this formula:**

**Alternative formula:**

### Explaining alternative formula in cell D3

=SUMPRODUCT(--($A$2:$A$10<=$D$2),--($A$2:$A$10>=$D$1))

**Step 1 - Create a boolean array with matching dates to the first criterion**

=SUMPRODUCT(**--($A$2:$A$10<=$D$2)**,--($A$2:$A$10>=$D$1))

--($A$2:$A$10<=$D$2)

becomes

--({39448;39450;39448;39474;39459;39473;39453;39452;39463}<=39463)

becomes

--({39448;39450;39448;39474;39459;39473;39453;39452;39463}<=39463)

becomes

--({TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE})

becomes

{1;1;1;0;1;0;1;1;1}

**Step 2 - Create a boolean array with matching dates to the second criterion**

=SUMPRODUCT(--($A$2:$A$10<=$D$2),**--($A$2:$A$10>=$D$1)**)

--($A$2:$A$10>=$D$1)

becomes

--({39448;39450;39448;39474;39459;39473;39453;39452;39463}>=39452)

becomes

--({FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE})

becomes

{0;0;0;1;1;1;1;1;1}

**Step 3 - All together**

=SUMPRODUCT(--($A$2:$A$10<=$D$2),--($A$2:$A$10>=$D$1))

becomes

=SUMPRODUCT({1;1;1;0;1;0;1;1;1},{0;0;0;1;1;1;1;1;1})

becomes

=SUMPRODUCT({0;0;0;0;1;0;1;1;1}) returns 4.

### Download Excel *.xls

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

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

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

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

Count specific WEEKDAYS between two dates

If you want to count specific weekdays like for example Mondays and Wednesdays you need a more complicated array formula. […]

7 days (weekly) date ranges using a formula

Shannon asks:I need a formula that if I enter a start date in field B1 such as 6/8/11 it will […]

How to calculate the number of years between dates

The image above demonstrates the DATEDIF function calculating the number of complete years between two dates. Column B and column C […]

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

The DATEDIF function in cell E3 allows you to calculate days between two dates. Weekly Blog EMAIL Email Welcome! I […]

### 22 Responses to “Count dates inside a date range”

### Leave a Reply to Oscar

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

This did a great job of giving me what I needed, with a minor tweak in that I took out the $ in $D$2 and $D$1 and replaced with D2 & D3, respectively.

In case someone might ask why, it easily replicates the formula multiple times by copy/paste methods. Otherwise, copying and pasting into the next cell to total the difference between the next two dates on a long list of dates will simply copy the same exact formula 30 timess. The $ make the cell range entered constant, ignoring Excel's internal mechanisms that would otherwise tell the formula to automatically adjust to the new formula location (d3&d2, d4&d3, d5&d4, etc.). You still want to use the ctrl+enter, though. I had about 300 rows with dates ranging over two months to work with.

Thanks so much!

Thanks for your comment!

Great job, I've seen some posts regarding this this subject but your SUMPRODUCT solution is the more elegant and versatile I've found!

Thumbs up and thanks!

Any chance this would work with multiple parameters.

I need to count how many times JA appears in a given period 1-Sep-10 and 10-Sep-10

A B

1 JA 2-Sep-10

2 SH 11-Sep-10

3 JA 3-Sep-10

I can't seem to link CountIf(A1:A3,"JA") and the Sum(If) part

any help greatly appreciated.

Sam,

read this post: Count records between two dates with multiple parameters in excel

thanks man, worked a treat

I can't seem to take this work. I am using the SUMPRODUCT formula. The only difference is that the dates to count are on another sheet within the workbook, but I would doubt that would be the issue. I keep getting the #NUM! error, but I have double verified that all reference cells are formated as dates. Please help.

Excel stores dates as a number representing the number of days since 1900-Jan-0 This is called a serial date, or serial date-time.

Source: https://www.cpearson.com/excel/datetime.htm

Are all referenced cell values serial dates?

How can i tell. I did read the article and played with the 1904 system indicator under options, but none of that made the formula work.

Marina,

Select a date cell.

Press Ctrl + 1.

Select "Category:" General.

If sample data shows you a number like 40494 (today) then you know it is a serial date.

Good work guys, but I require little different solution.

In A column, there are dates in mmddyyy format and in B column, there are two variables uses either "PASS" or "FIAIL". All I want to do is to count the "PASS" in individual month range.

Can someone help me in it. I am able to count days of month from the column A but can not link it with Column B.

Regards,

Johny

Janib Soomro,

Read this post: Count matching cells in date range in excel

thanks, I need to count in a list the interval between the same value, example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 so the answer must be for the value 1 the spaces are 2,2,6. thank you

jeyner,

read this post:

Excel: List intervals between two values

how do i count cells to determine how many cells occured on jan 2, 2011 from 2 am and 6 am.. which is housed in one of my columns in my excel workbook.

Very nice indeed! How would I go about averaging a column of data (instead of counting) based on a start and end date?

Nevermind. I found this link on your site, and just changed the MAX function with the AVERAGE function and it appears to be correctly working. Thanks again!!

Forgot the link in case others need it:

https://www.get-digital-help.com/2009/11/11/lookup-min-max-values-within-a-date-range-in-excel/

Tom,

Thanks, I am happy you found what you were looking for!

Hi Oscar,

Can this be applied using time as start and end parameter?

For example column A 12:01AM, column B 1:00AM

01:01AM 2:00AM and so on.

Thanks

How do I set up an IF,Then function/ or a macro (not sure what would be better) to do the following very complicated thing... I have a spreadsheet where I am monitoring KPI (dates of certain events listed by job)... there are 5 dates that are being monitored to make sure they fall within the expected cycle times for the company. So... I want to set up an IF, then formula that lists IF there is no date listed in a field then in another column (where I put the formula) I would like it to use my header's (for the missing field) as text, then count the dates outstanding from the day the job was opened (in column C) ie.=TODAY()-C2

I'm not sure how to set the formula, but I want the end result to look something like, "Estimate is 133 days past due" being pulled from the values "[blank field's column header] is [=(TODAY()-C2)-Cycle Time Expectation#)] past due"

Christina,

Can you provide some example data and the desired outcome or upload a workbook?