You won’t believe how easy it is to use COUNTIFS in a time range in Google Sheets. It’s not rocket science, but there’s a catch when you want to use COUNTIFS in a date and time column, also known as the timestamp or DateTime column.
In this article, I’m going to share some essential tips for using COUNTIFS in both a time column and a date and time column to count a specific time range. And don’t worry, I’ll walk you through it step by step.
How to Use COUNTIFS in a Time Range in Google Sheets
Let’s break it down into two parts. In the first part, I’ll show you how to use COUNTIFS in a time column to count the time range. In the second part, we’ll dive into using COUNTIFS with a timestamp or DateTime column.
The Use of COUNTIFS in a Time Column
To start, let’s look at an example formula for using COUNTIFS in a time range in Google Sheets:
=countifs(A2:A8, ">=08:30:00", A2:A8, "<=09:30:00")This formula counts the number of times between 8:30 AM and 9:30 AM (inclusive). If any time in column A falls within this range, it will be counted.
Alternatively, you can enter the criteria in separate cells (e.g., E2 and F2) and refer to them in the formula:
=countifs(A2:A8, ">="&E2, A2:A8, "<="&F2)Easy, right? Counting time by duration is straightforward when dealing with a time column. However, things can get a little trickier if your column contains timestamps instead of pure time.
The Use of COUNTIFS in a DateTime (Timestamp) Column
What if you want to count a time range when the time is in a date and time format, also known as a timestamp? Here are a few options you can try:
Formula 2: Using Query+Split to Split DateTime
If your column contains timestamps, you can’t use it as the criteria range in COUNTIFS. You’ll need to extract the time from the date using a combination of the QUERY and SPLIT functions:
=ArrayFormula(COUNTIFS(query(split(A2:A, " "), "Select Col2"), ">=8:30", query(split(A2:A, " "), "Select Col2"), "<9:30"))In this formula, we split the DateTime column into two columns: one for the date and another for the time. Then, we use the QUERY function to select the second column, which contains the time.
Formula 3: Using Time Functions to Extract Time from DateTime
Another option is to use time functions to extract the time from the DateTime column. Here’s an example formula:
=ArrayFormula(COUNTIFS(TIME(hour(A2:A8), minute(A2:A8), second(A2:A8)), ">=8:30", TIME(hour(A2:A8), minute(A2:A8), second(A2:A8)), "<9:30"))This formula uses the TIME function to extract the hour, minute, and second from the DateTime column and create a new column with just the time. Then, we can use COUNTIFS as before to count the time within the specified range.
Formula 4: Using Replace Formula to Extract Time from DateTime
Finally, my favorite formula for counting time segments in a DateTime column involves using the REPLACE function:
=ArrayFormula(COUNTIFS(value(replace(A2:A8, 1, 11, "")), ">=8:30", value(replace(A2:A8, 1, 11, "")), "<9:30"))This formula replaces the date in the DateTime column with an empty string, effectively extracting just the time. It’s a cleaner solution compared to the previous options.
That’s it! Now you know how to use COUNTIFS in a time range in Google Sheets, whether you’re working with a time column or a DateTime column. Enjoy exploring the possibilities!
For more helpful tips and tricks, visit Crawlan.com.
