Solved

excel formula

Posted on 2014-12-01
8
87 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?
0
Comment
Question by:Rrave26
  • 4
  • 3
8 Comments
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 40474145
you should use the time function there

TIME(0,15,0)  like this
0
 

Author Comment

by:Rrave26
ID: 40474154
I am sorry, use it where?  In my countifs formula?
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40474169
instead of using this "0:15:00"  use TIME(0,15,0)
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:Rrave26
ID: 40474304
I am confused, I have used a formula already to calculate this time frame.  How would I incorporate this time formula into this?
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40474367
=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))
0
 

Author Comment

by:Rrave26
ID: 40474380
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.
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40474423
any chance of a sample sheet to look at here?
0
 

Author Closing Comment

by:Rrave26
ID: 40474592
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.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question