Count entries based on date and time
Author: Oscar Cronquist Article last updated on January 30, 2019
My issue is that I get the date in this format:
7/23/2011 7:00:00 AM
I am trying to count how many entries are between date and time. So I have a shift that starts at 6:30:00 PM and leaves at 7:00:00 AM the next morning. I have tried to convert that to a value to no avail.
I have the cells formatted correctly and tried your other formula =SUM(IF(($A$2:$A$10$D$1),1,0)) + CTRL + SHIFT + ENTER but that returned all records.
I am starting to think that Excel (I'm using 2010) cannot differentiate between the date and time. Any ideas would be greatly appreciated.
Answer:
Formula in cell F6:
=COUNTIFS(B3:B17, "<="&F3, B3:B17, ">="&F2, C3:C17, F4)
Explaining formula in cell F6
The COUNTIFS function was introduced in Excel 2007 and it works like the COUNTIF function except you may use multiple conditions at the same time.
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
| Pair | criteria_range | criteria | Text |
| 1 | B3:B17 | "<="&F3 | Are dates in B3:B17 smaller than end date in cell F3? |
| 2 | B3:B17 | ">="&F2 | Are dates in B3:B17 larger than start date in cell F2? |
| 3 | C3:C17 | F4 | Are Items in c3:C17 equal to cell F4? |
The ampersand character concatenates the logical operators <> and = to each cell or cell range before the COUNTIFS function evaluates the argument. If all conditions return TRUE then the record is counted as 1.
Step 1 - Criteria pair 1
The following image shows in column D date and time entries smaller than or equal to condition in cell F3, TRUE - Smaller, FALSE - larger.
Step 2 - Criteria pair 2
This image shows in column E date and time entries larger than or equal to condition in cell F2, TRUE - Smaller, FALSE - larger.
Step 3 - Criteria pair 3
This picture displays in column F items equal to condition in cell F4.
Step 4 - All conditions applied
This image shows which entries meet all conditions. If all conditions evaluate to TRUE then that specific record is counted, the formula returns 2 in cell F6 because two records meet all conditions.
Excel 2003 (and earlier versions) formula in cell F6:
=SUMPRODUCT((B3:B17<=F3)*(B3:B17>=F2)*(C3:C17=F4))
Count values category
Count unique distinct values that meet multiple criteria
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
Count unique distinct values
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
Count Conditionally Formatted cells
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
Count cells between specified values
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
Count unique distinct values in two columns
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]
Count unique distinct values in a filtered Excel defined Table
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
How to count unique distinct values based on a date
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
Count unique distinct values within same week, month or year
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
Count unique distinct values based on a condition
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
Count comma separated values
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
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 […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
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 […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
Count unique distinct records with a date and column criteria
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
Count cells based on background color
This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]
This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]
Count cells between two values
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]
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. […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
How to count repeating values
This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]
This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]
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 […]
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
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 […]
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
Dates category
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 […]
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 the most recent date that meets a particular condition
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 return the latest date based on a condition using formulas or a Pivot Table. 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 […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
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 […]
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]
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 […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Identify overlapping date ranges
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
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 […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
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 […]
This article explains how to find the smallest and largest value using two conditions. In this case they are date […]
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 […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
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 […]
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
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 […]
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
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 […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
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 […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
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 […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
Filter unique distinct values based on a date range
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a 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: […]
Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]
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. […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
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 […]
The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]
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 […]
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]
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 […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
Functions in this article
More than 1300 Excel formulas
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.
3 Responses to “Count entries based on date and time”
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.
How can I format a negative time format -00:00 and then show it in a Chart from -04:00 to 04:00?
Awesome... Thanks! I've been looking for this example for days now..
If you wanted to add a further count into C for example. So you are counting between a date, a certain criteria and then the amount how would you go about this? using example 1 above if ITEM A was sometimes a square and sometimes a circle. How would you count this extra criteria?
Thank.s