Unsure of formula for data with multiple criteria

[NoirEnkame]

Hello,

Having some difficulty in formatting a formula for the data that I have. I have multiple dates within in a month that have numerous calls at various hours. I need to determine how many calls we received for certain amount of hours for a certain number of days. IE.

07/04/12 10:58:53 AM Wed

07/04/12 11:00:18 AM Wed

07/06/12 08:13:13 AM Fri

07/08/12 10:16:50 AM Sun

07/09/12 18:31:45 PM Mon

I need to my formula(s) to tell me that we have receive 2 calls during work hours,2 calls during the weekend, and 1 call afterhours.


I was originally thinking that I can separate the date and time and equate each with a value then create a formula that would say if A1=1 and B1=1 then C1=1 call during working hours and make something separate for the other fields. A1=2 and B1=1 then C1-1 call during after hours, then total those up. However; that would take way too long, so any help would be greatly appreciated! Thanks.

 

[Hui]

NoirEnkame


Firstly, Welcome to the Chandoo.org forums.


Can you upload a small sample

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook


I want to make see if your date/times are true Dates/Times or Text

 

[NARAYANK991]

Hi ,


One or two points :


1. The time of 18:31:45 PM is clearly not a time value ; if you add any time value such as "03:00" to the times , all of them give correct time outputs except this one ; this needs to be corrected to 6:31:45 PM , which will also result if you just enter 18:31:45 , and format the cell as h:mm:ss AM/PM.


2. I assume Monday is the after work hours day ; regarding the other days , can I assume that Wednesday was a working day , and Friday and Sunday were the weekends ? If not , can you explain which days are to be taken as the weekend days ?


Narayan

 

[NoirEnkame]

Hi!

Sorry, I am completely brand new to this whole blog thing, thank you for the welcome!


Well here is the link https://docs.google.com/spreadsheet/ccc?key=0AoCQxgPm3dd5dHFyZ2RlbkJZZWpnNm5BbndZbnpZWmc


I have never created a online doc so, if it does not work correctly I do apologize and will try it again.


I believe that the original file that I import creates the data under the custom category, in addition when I originally posted the blog the data that I used had some numbers created by me so that is why 18:31:45 may not have worked properly.


Working days are 7a-17:30p M-Thur, After hour calls are 17:31p-6:59a M,T,W and Weekend calls are Thurs after 17:30, all day fri,sat,sun and monday before 7am. Thank you for all the help!

 

[NARAYANK991]

Hi ,


Thanks for uploading the data ; the data is all OK , except that the formatting is to changed to :


m/d/yyyy hh:mm:ss AM/PM


We can do the following :


1. If the WEEKDAY function is used , we get SUNDAY if it is 1 , MONDAY if it is 2 ,...


2. If the WEEKDAY function returns 4 , and the time is after 17:30:00 , it is a WEEKEND till the WEEKDAY function returns 2 and the time is greater or equal to 07:00.


3. For any WEEKDAY value = 2 or 3 , and the time is greater than 17:30 , and less than 23:59:59 , it is AFTER HOURS.


4. For any WEEKDAY value = 3 or 4 , and the time is less than 07:00 , it is AFTER HOURS.


5. All other times are WORK HOURS.


Can you implement this using the IF function ?


It'll become easier if you use helper columns for the three categories of WEEK ENDS , AFTER HOURS and WORK HOURS.


Narayan

 

[NoirEnkame]

Thanks for the info, I am still trying it out and I know that it is pretty primative, but what I have accomplished so far is separating the date (col a) and time (col b) then setting up the weekday function (col c) to determine (col a),and a if funcion to say work hours and not work hours in col (d) to determine (col b). Next I will try a formula in (col e) that will look at both col d and e and populate an answer within the 3 buckets that I am looking for. Then have another formula that will tally all of these.


However; the one draw back that I am having is that, I plan on trying to make this run automatically and since the amount of data is difference every month I normally make my formulas run to 20000. What I noticed is that in the weekday column when there is no data to refer to (col a) then 7 will automatically appear. Can I not get rid of that? Thanks. Sorry for it being so lengthy and being patient with me

 

[NARAYANK991]

Hi ,


I'll upload your file in the next half hour.


Narayan


P.S. Can you check this file ?


https://docs.google.com/open?id=0B0KMpuzr3MTVekxILUwzZS1FUTg


It is possible that the input data in column A may be different from what you get from your system at present ; however , you can go through the formulae in the other columns and verify that they reflect your stated logic correctly. Once this is done , then the remaining point will be to ensure that the input data is converted to a valid date-time value. This is a simple matter.

 

[NoirEnkame]

WOW! Thank you soo much. I have been adjusting the formulae in the other columns and have been able to get it exactlly what I need the document to show. You have been instrumental in helping me get this done. I don't know if there is a place where we can rate the helpfulness of users but you would get a 11 out of 10, and I will definately be recommending chandoo!