We help IT Professionals succeed at work.

excel formula

Rrave26
Rrave26 asked
on
108 Views
Last Modified: 2014-12-01
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

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

Author

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

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

Author

Commented:
I am confused, I have used a formula already to calculate this time frame.  How would I incorporate this time formula into this?
Professor JMicrosoft 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))

Author

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.
Danny ChildIT Manager

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

Author

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.
Join our community and discover your potential

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.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.