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.
Get Excel *.xls
count-records-between-two-dates-in-excel.xls
Dates basic formulas category
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 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
Excel categories
22 Responses to “Count dates inside a date range”
Leave a Reply
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.
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?