Excel VBA Course
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.)

View Course

Countifs not working for both date and time criteria

0

Hello All,

I've has a "TaskName" in column M and "ActiveDate" in Column N.

"Active Date" is a number and with format as 5/31/2018  10:46:14 AM

I'm trying to count "date and time range", buy my date criteria and time criteria are in different cells.

This the formula I have used which is giving me incorrect values.

=COUNTIFS(TaskNm,F$7,ActvDt,">="&$E$4+$D8,ActvDt,"<"&$H$4+$E8)

F7 = TaskName (I'm difined name to it as shown in formula

E4 & H4 are dates

E4 & E8 are Times

Please someone help me to  solve my problem. Please use the attachment its very simple to read and understand

Thank you and your solution will be great help

excel
posted by: narendra_749yahoocom (rep: 14) on Sun Jun 24, 2018 at 3:14 pm
Post Edited
Title Not Appropriate: Title was not appropriate. (could be all caps, annoying punctuation, etc.)
Answer
Discuss

Answers

0

OK. Here is the proof that your own earlier formula was correct.

First, I tried to show that the cell value of 31.05.18 12:00AM and 43251.0 is identical. Columns N and P are identical in value but different in format. When formulas refer to them they usually look at the value (there are a few exceptions not applicable here). Further proof is in the rows 5 and 6 as well as the column pairs Q:R and T:U. Q:R just ckecks if N >= [Start date]. T:U checks if N < [End date]

Row 9 counts the number of True results in the columns Q:U. X9 counts how many are True in both columns (Q & T or R & U). Finally, X11 adds one more condition to the count. That formula (X11) returns exactly the same result as your own formula in AA11.

You can now expand the proof by counting the number of entries (A) between 31.05.18 12:00AM and 31.05.18 01:00AM and (B) between 25:06.18 12:00AM and 25.06.18 01:00AM. You can modify the test data in rows 5:6 for this purpose, leaving the formulas in place unchanged and observe the counts in row 9 and 11. Logically, 'Order Receival'!F9 = 'Order Receival'!F8 - A + B because the counting period is shifted by just one your. Note that tab Order Receival doesn't work in the attached sheet because of the changes I made to the named range on the Raw Data sheet.

Unfortunately, since your earlier formula is proven correct, the formula you later posted is wrong although you preferred its result. Apparently you expect single digit results from your count. For such a result you should rephrase the task, I suggest like this:-

"Count all entries between 31.05.18 and 25.06.18 which were made between 12:00AM and 01:00AM." I have aded a formula to do that count in AA13. It needs the helper column you had already suggested and which I added back in column Y.

posted by: Variatus (rep: 4889) on Sun Jun 24, 2018 at 9:12 pm
Discuss

Discussion

Thank you so much!!
narendra_749yahoocom (rep: 14) Jun 25, '18 at 3:09 am
Add to Discussion
0

Hi Variatus,

Thank you so much!! You are an awesome Excel Guru.

I cracked it by using 1 Helper cell.... I was trying it for 2 days and was struggling to crack it. But I remembered to take help from this site as I did it once. 

Well, I just two test helper cell to evaluate the conditions of "Active Time". Then I incorporated those conditions in Countifs. 

As you suggested I evaluated TRUES and FASLSES suggestion and cracked it

By inducing below formula:

=COUNTIFS($M$2:$M$165,AU$8,$N$2:$N$165,">="&DATE(YEAR($AS$5),MONTH($AS$5),DAY($AS$5)),$O$2:$O$165,">="&TIME(HOUR($AS10),MINUTE($AS10),SECOND($AS10)),$N$2:$N$165,"<"&DATE(YEAR($AT$5),MONTH($AT$5),DAY($AT$5)),$O$2:$O$165,"<"&TIME(HOUR($AT10),MINUTE($AT10),SECOND($AT10)))

Sorry, I haven't used define name for arrays….

If you can review the attachment and give me any better solution would be an another great learning from me.

Thank you so much once again.

posted by: narendra_749yahoocom (rep: 14) on Mon Jun 25, 2018 at 3:08 am
Discuss

Discussion

Please follow the rules and edit your original question with the updated information. Then, if you have comments to make about any Answers, click the Discuss button under that Answer.
don (rep: 1989) Jun 25, '18 at 3:28 am
Sorry Nrendra, I think you didn't nail it. This is your test formula in P3 =AND(N2>=DATE(YEAR($AS$5),MONTH($AS$5),DAY($AS$5)),N2>=TIME(HOUR($AS$9),MINUTE($AS$9),SECOND($AS9))). Its component =DATE(YEAR($AS$5),MONTH($AS$5),DAY($AS$5)) evaluates to 31.05.18 or 43251 which is the same value as AS5. The other component =TIME(HOUR($AS$9),MINUTE($AS$9),SECOND($AS9)) evaluates to 12AM or 0, and that value is the same as AS9. Replacing the functions in your formula with hard numbers it says, =AND(N2>=43251,N2>=0). The calculation confirms TRUE because N2 is later that 31.5.18 and greater than 0, but the sense eludes me. The formula in AU9 has the same illogic.
I think your earlier concept of adding the time to the date integer was correct because it gives a correct date/time number. Please explain why you want to count the number of entries between 12AM on May 31 and 1AM on June 25. I can see the sense of counting entries between May 31 and June 25, or between 12AM and 1AM on any given day, but not 25 days + 1 hour. Moreover, the next result, 1AM on May 31 till 2AM on June 25 must count a great many entries also included in the batch that started an hour earlier.
Variatus (rep: 4889) Jun 25, '18 at 4:17 am
Hello Variatus,

I checked the result is the table which I've created. Please manually count the result shown in the table and you will notice that the answeres are correct. Hence I felt that I've cracked it.

Thank you!!
narendra_749yahoocom (rep: 14) Jun 25, '18 at 2:49 pm
Hi Variatus

I also had posted this on excel forum for answer. I had posted the same answer which I got i.e., using 1 helper column and countifs. FlameRetired  saw my answer and give me this formula without helper cell

=SUMPRODUCT(--($M$2:$M$165=AU$8),--(INT($N$2:$N$165)>=$AS$5),--(MOD($N$2:$N$165,1)>=$AS9),--(INT($N$2:$N$165)<$AT$5),--(MOD($N$2:$N$165,1)<$AT9))

He had used the same logic using what I've used in countifs..(Counting trues and falses)

Hope this will help you understand it.
narendra_749yahoocom (rep: 14) Jun 25, '18 at 4:08 pm
The SUMPRODUCT function was used for this sort of thing before they created the SUMIFS function in 2007. SUMIFS is easier to handle.
I suggested the helper column not as a permanent feature but to verify the result of the formula you were trying to create in a more transparent manner. Once the result is verified the helper column is no longer required.
Your partial formula N2>=TIME(HOUR($AS$9),MINUTE($AS$9),SECOND($AS9))  will always evaluate to TRUE because any TIME value is smaller than 0 and N2 is always greater than 1. Therefore your formula evaluates only one of the criteria instead of two.
I would like to help you find a SUMIFS formula to meet your expectations. In order to do so I would need to understand your requirement. If you want me to keep trying please confirm that you wish to count the entries between 31 May 2018 12:00AM and 25 Jun 2018 01:00AM and, in the next row, between 31 May 2018 01:00AM and 25 Jun 2018 02:00AM etc.
Actually, I think your own formula does that count correctly. I believe the description of the count is wrong. But if you confirm the description I will either prove that your formula is correct or find any mistake it might contain.
Variatus (rep: 4889) Jun 25, '18 at 9:05 pm
Hi Variatus,

Yes, you are right I want to count as you discribed.

Count the entries between 31 May 2018 12:00AM and 25 Jun 2018 01:00AM and, in the next row, between 31 May 2018 01:00AM and 25 Jun 2018 02:00AM etc.
narendra_749yahoocom (rep: 14) Jun 26, '18 at 12:56 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login