Count entries based on date and time
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:
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:
Count values category
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
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 […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
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 E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
Dates 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 […]
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
This article explains how to find the smallest and largest value using two conditions. In this case they are date […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains 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
Excel categories
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