asked on

excel formula

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?

ASKER CERTIFIED SOLUTION

Professor J

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Rrave26

ASKER

I am sorry, use it where? In my countifs formula?

Professor J

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

Rrave26

ASKER

I am confused, I have used a formula already to calculate this time frame. How would I incorporate this time formula into this?

Professor J

=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))

Rrave26

ASKER

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 Child

any chance of a sample sheet to look at here?

Rrave26

ASKER

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.