Solved

excel formula

Posted on 2014-12-01
8
88 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 26

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 26

Expert Comment

by:ProfessorJimJam
ID: 40474169
instead of using this "0:15:00"  use TIME(0,15,0)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 26

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

763 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