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:
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]
IF with AND function – multiple conditions
The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. […]
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 […]
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 describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
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
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
Table of contents Match a date when a date range is entered in a single cell Match a date when […]
Highlight specific time ranges in a weekly schedule
In a previous post I created a simple weekly schedule with dynamic dates, in this post I am going to […]
I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for […]
Column C contains both date and time, to calculate the average of only time we need to extract the hours, minutes […]
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