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 explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
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 […]
Functions in this article
More than 1300 Excel formulas
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