We help IT Professionals succeed at work.

# excel formula

on
108 Views
I want to calculate the time it took to send out a communication.  I have the engaged start time, cell AR and the sent time cell, AU.  Since there are times when we can be engaged but no communication is sent I created the following formula to calculate the sent time:
=IF(AU2<AR2, 0,AU2-AR2)
The results are put into field column BB and formatted h:mm:ss

Now I have created another formula to count the total number of communications that are sent in the month:
=COUNTIFS('IM Raw Data'!\$J:\$J,">="&"11/01/2014",'IM Raw Data'!\$J:\$J,"<="&"11/30/2014",'IM Raw Data'!\$G:\$G,'HTSN&HBCA'!\$A6,'IM Raw Data'!\$H:\$H,"HBUS").  This formula works correctly and return 28 records.

I have another to count the total number that sent within 15 mins:
=COUNTIFS('IM Raw Data'!J:J,">="&"11/01/2014",'IM Raw Data'!J:J,"<="&"11/30/2014",'IM Raw Data'!H:H,"HBUS",'IM Raw Data'!BB:BB,"<="&"0:15:00").  This returns a value of 22 when it should be returning a value of 23.

What am I missing here?  How can I error check column BB to make sure that my values are being counted correctly?
Comment
Watch Question

## View Solution Only

Microsoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

Commented:
I am sorry, use it where?  In my countifs formula?
Microsoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Commented:
instead of using this "0:15:00"  use TIME(0,15,0)

Commented:
I am confused, I have used a formula already to calculate this time frame.  How would I incorporate this time formula into this?
Microsoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Commented:
=COUNTIFS('IM Raw Data'!J:J,">="&"11/01/2014",'IM Raw Data'!J:J,"<="&"11/30/2014",'IM Raw Data'!H:H,"HBUS",'IM Raw Data'!BB:BB,"<="&time(0,15,0))

Commented:
Ok, I see what you are getting at.  I have tried that and there is no change in the result.  I still show 22 records when I should have 23.
IT Manager

Commented:
any chance of a sample sheet to look at here?

Commented:
Ok, I used the function a bit differently in that I used it in an if statement and just counted the positive, true, results in the formula and that fixed it.
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.