I have to count time between specific range sorting by DAY.
I have to count time between specific range sorting by DAY.
This is kind of a fun one:
=COUNTIFS(B:B,"Thu",A:A,">.375",A:A,"<.385417")
We use the COUNTIFS() function to count based on multiple criteria.
To get the decimal numbers for the time, just input time in a correct time format into a cell and then convert that cell to the General format and you will see the decimal number, which is how Excel stores time values.
Update: File with formula attached.
Unfortunately you don't tell us where you want to use the result. Therefore it's difficult to use existing data to specify the formula. In the solution below I have presumed that you will want to specify the 3 criteria somewhere. I chose C1:C3. Please enter
[C1] ="Thu"
[C2] = 9:00:00
[C3] = 9:15:00
Now the formula below will produce the correct result if entered in any cell in the worksheet.
=COUNTIFS($B$6:$B$1000,$B6,$A$6:$A$1000,">="&$C$2,$A$6:$A$1000,"<"&$C$3)
You can have an even better formula if you make a little more preparation. Create the following two named range.
A6:A100 = Times
B6:B100 = Days
Now you can refer to the ranges by name.
=COUNTIFS(Days,$C$1,Times,">="&$C$2,Times,"<"&$C$3)
Bear in mind that when you define the range as A:A Excel will have to examine some 1.4 million cells. If you know that you will never have more than 1000 rows in your worksheet, $A$6:$A$1000 is an improvement. But if you name the range you can change the size quickly in only one location, the Names Manager. You can even specify a dynamic range which adjusts itself to how many rows are used. Just bear in mind that both ranges must be of equal size.
09:15:00 isn't equal to 0.385417 days. It's equal to 0.385416666666 days. Therefore 09:15:00 is smaller than 0.385417 and will therefore be included in the returned count. But that isn't necessarily what you want.
Normally, one would count from 9:00 to 9:15 as meaning >=9:00 and <9:15 so that the next groupd can start from 9:15 and end before 9:30. In this scheme 9:15 must be excluded from the frist group and included in the second. By entering the times in C2:C3 you get precise numbers which the formula can evaluate 100% correctly.