Count with multiple criteria (Date, Time and Other Criteria)

0

Hello There,

The table shown in the attachment is designed to get desired result from raw data. (I've attached file for reference).. Please help me

I'm unable to use date criteria along with time to get desired result.

Note: Date and Time are part of same rows and I also have additional criteria along with date and time.

Example of Date and time format used in raw data: 5/31/2018  10:46:14 AM

I've used helper cell to  extract time from date and time row and below is the formula I came up with. I'm not able to insert date criteria within it.

=(COUNTIFS('Raw Data'!$M$2:$M$9999,F$5,'Raw Data'!$AO$2:$AO$9999,">="&$D6))-(COUNTIFS('Raw Data'!$M$2:$M$9999,F$5,'Raw Data'!$AO$2:$AO$9999,">"&$E6))

Sorry, I'm unable to paste result table here  (Please use attached file)

Answer
Discuss

Answers

0

I admit to not fully understanding your problem but I feel it is self-created by your lack of understanding of Excel's handling of Date and Time. Your Helper columns seem to destroy your data. Please try if the following helps.

Entering =NOW() in a cell and formatting that cell as General will display a number like 43275.83088 in which the integer indicates a date and the decimals the time. Different cell formatting will change the display. To get a feel for it, try dd/mm/yy hh:mm or d mmm yyyy hh:mm AM/PM or simply ddd (all Custom formats). The display in your Helper column OA could be simply produced with a cell format of hh:mm AM/PM without changing the cell value. However, if you wanted to change the value to exclude the date the simpler formula to use would be =MOD(N2, 1) instead of =IF(ISNUMBER(N2),TIME(HOUR(N2),MINUTE(N2),SECOND(N2)),"").

Of course, when you exclude the date from the cell's value you lose the ability to calculate time differences that span days, such as 11 pm to 1 am (next day). Similarly, counting values between, say, 43275.50 and 43275.75 (12 noon and 6 PM on June 24, 2018) would be a simple matter. The thing to understand is that the cell values remain the same whatever the cell format prescribes for their display.

I would also urge you to replace range references like 'Raw Data'!$M$2:$M$9999 with a named range like TaskNm. Your formulas would become must easier to read, much shorter and you would have the added bonus of being able to define the named range as dynamic so that it grows and shrinks with the number of rows actually used, and that would make your worksheet calculate faster.

Please see how far you can get with the above advice. Let me know where you get stuck after implementing a system of dealing with Date/Time values the way Excel intended that you should.

Discuss

Discussion

Thank Variatus for advice. I know the fact that date values are int and time value are decimal.

I used define name as you suggested and used below shown formula and found no result.

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

F7 = TaskName (I've difined name to it as shown in formula)
E4 & H4 are dates
E4 & E8 are Times

Please help me to get the desired reslult based on the table. I've updated the attachment and it looks very simple and easy for you now to understand.
narendra_749yahoocom (rep: 14) Jun 24, '18 at 1:44 pm
Add to Discussion

Answer the Question

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