I can create a raw report from Cisco Unified CCX Historical Reports that has all calls to a group and I need to determine how may call were received during set time periods throughout the day 8 am to 10 am to 12 pm to 2 pm and so on I have tried numerous formulas with no luck it is possible my Wizard hat is not tall enough nor does it have enough stars to figure this out
thanks in advance
Bill
I have included a spreadsheet TSCTIMEEXAMPLE.xlsx
Got the spreadsheet, where would you like the answer and how do you want it to look...a couple of hand coded examples of expected output would help immensely
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
Hi Wallardyce and welcome to the forum,
Look at Column D for a new "Helper" column and the formula I think you need. Your times need a space between the last number and AM or PM. That is the first problem. Then converting the to time and rounding down to two hour shifts.
See the big formula in D2. I've also done a Pivot Table and Chart so you can filter the group and see the times.
=HOUR(TIMEVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(C2,"AM"," AM"),"PM"," PM"),11,25)))) - MOD(HOUR(TIMEVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(C2,"AM"," AM"),"PM"," PM"),11,25)))),2)
One test is worth a thousand opinions.
I went into the report generator and corrected the format for the date and time to add a space before am and pm
I a running a report now that goes back one year
I think what you came up with is going to work, I will let you know
thanks gain
Hey -
It's great to hear I might have a good answer every day or two. Hope it saves you time and makes you learn new stuff!
After changing the format of the date to add a space before am and pm your formula does not work
what do I need to remove that does the compensation for the lack of a space?
I guess you have to show me what you have that doesn't work.
Check to see there is a single space after the time and before the AM or PM. I guess I need to see what you have to understand the question now.
Here is what I am getting after formatting the report to add a space before am and pm
Why make thing complicated???
=HOUR(TIMEVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"AM"," AM"),"PM"," PM"),11,25)))) - MOD(HOUR(TIMEVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"AM"," AM"),"PM"," PM"),11,25)))),2)
It's very simple formula:
=HOUR(A1)-MOD(HOUR(A1),2)
Last edited by Teethless mama; 12-11-2012 at 10:34 PM.
from your descriptions, i gather that you are looking to find the total number of calls between 8 AM to 10 AM, and 12 PM to 2 PM.
if that is the case, you can try to use this formula in the context of the file that you uploaded in post #8 above:
1007=SUM(INDEX(((MOD(A1:A10,1)>=TIMEVALUE("08:00:00"))*(MOD(A1:A10,1)<TIMEVALUE("10:00:00"))) + ((MOD(A1:A10,1)>=TIMEVALUE("12:00:00"))*(MOD(A1:A10,1)<TIMEVALUE("14:00:00"))),0))
- i.s.z -
CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
All good ideas are courtesy resources from this forum as well as others around the web.
- e.o.m -
Does not seem like it worked
Hi wallardyce,
I needed to put a space in front of the AM and PM to make the conversion to time work. The reason it didn't work for you the second time is that you ran the formula on cells that already had the space in front of the AM and PM. You said you got the data (without the space) from a text file import.
Does this help your answer?
I changed how Crystal Report did the date and time format to include a space before am/pm so excel actually thinks it is date/time
I have a years worth of data to crunch and was trying to determine what days/time of day had the most call activity
Bill
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks