I have to count time between specific range sorting by DAY.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
count time by day
0
Answers
0
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.
- Text value is "Thu" for Thursday
- Time value is greater than .375 (9:00 AM)
- Time value is less than .385417 (9:15 AM)
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.
Discussion
How u caclculate 9 am as .375 and 9.15 am as .385417??
plz suggest..
plz suggest..
himanshusd (rep: 4) Jan 29, '19 at 11:24 pm
Im sorry, I thought I made that clear, go into Excel and do this: input 9:00 AM into a cell and then input 9:15 AM into another cell and Excel should recognize them as a time. Then select those two cells and change the format of them to General - select the cells > right-click over them > click Format Cells > on the Number tab, select General from the Category section and hit OK.
Once you do this, you will see the decimals replace the time; Excel stores times as decimals.
Once you do this, you will see the decimals replace the time; Excel stores times as decimals.
don (rep: 1989) Jan 29, '19 at 11:34 pm
Don, your formula doesn't provide the correct result. Please check.
Variatus (rep: 4889) Jan 30, '19 at 3:09 am
I'm pretty sure that mine works, checkout the attached file now.
don (rep: 1989) Jan 30, '19 at 9:43 am
There are two flaws with your formula. (1) It will exclude 9:00 because it requires times to be grater than that. (2) It will include 9:15 because 9:15 is smaller than 0.385417 (it's 0.38541666666667). That might be pardonable if it were consistent but, as you see, the inclusion is a fluke. You should expect your formula to never return a predictable result.
Variatus (rep: 4889) Jan 30, '19 at 8:08 pm
Hmm very interesting, I was aware of number 1 since he said "between" and not including but number 2 definitely I did not notice! Time in Excel is definitely one of the more interesting aspects of the program.
don (rep: 1989) Jan 31, '19 at 1:05 am
0
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:00Now 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 = DaysNow 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.