Counting Case/Leads Per Hour

In most businesses incoming calls are great!  It either means your marketing is working and customers are interested (Leads), or it means you have a chance to impress your customers with great service (Cases)!

The downside is you have no control over when your customers call.  It could be that they call a lot on Monday mornings and not much on Wednesdays.  Or, it could be that lunchtime any day is busy.  How do you figure out how many folks to have available to take calls?

Let’s assume that you only need to know how many calls (Cases) you get coming in per hour.  Let’s also assume that your phone agents are really good about creating a new case for each call as it comes in.  This means your CreatedDate can be assumed to be the date/time the call arrived at your office.

Ok, with those assumptions out of the way, you need to pull the hour out of the date/time field CreatedDate.  Create a formula field (number) with the following formula:

VALUE(MID(TEXT(CreatedDate),12,2))

This formula does several things (from the inside out):
1.) It turns the CreatedDate into a Text string.
2.) It pulls 2 characters out of that string (#12 & #13).
3.) It makes those two characters into a number.

Now you have a formula field that gives you the hour of the CreatedDate.  The only trouble is (and this is kinda ugly), that CreatedDate is in Greenwich Mean Time (GMT).

What do you do if you need it in Eastern Standard Time? Or, worse yet, Eastern Daylight Time? Eeek!

Have no fear!  Formula fields to the rescue!

We need another formula field that will be the number of hours to offset.  For example, if I’m in the Eastern time zone and it IS Daylight Savings Time, my offset from GMT will be 4 hours.  If it IS NOT Daylight Savings Time, my offset from GMT will be 5 hours.  I’ll create another formula field (let’s call it DST_Offest__c). Here’s the formula:

IF(
((CreatedDate >= Date(2003,4,6)) && (CreatedDate <= DATE(2003,10,25))) ||
((CreatedDate >= Date(2004,4,4)) && (CreatedDate <= DATE(2004,10,31))) ||
((CreatedDate >= Date(2005,4,3)) && (CreatedDate <= DATE(2005,10,30))) ||
((CreatedDate >= Date(2006,4,2)) && (CreatedDate <= DATE(2006,10,29))) ||
((CreatedDate >= Date(2007,3,11)) && (CreatedDate <= DATE(2007,11,4))) ||
((CreatedDate >= Date(2008,3,9)) && (CreatedDate <= DATE(2008,11,2))) ||
((CreatedDate >= Date(2009,3,8)) && (CreatedDate <= DATE(2009,11,1))) ||
((CreatedDate >= Date(2010,3,14)) && (CreatedDate <= DATE(2010,11,7))) ||
((CreatedDate >= Date(2011,3,13)) && (CreatedDate <= DATE(2011,11,6))) ||
((CreatedDate >= Date(2012,3,11)) && (CreatedDate <= DATE(2012,11,4))) ||
((CreatedDate >= Date(2013,3,10)) && (CreatedDate <= DATE(2013,11,3))) ||
((CreatedDate >= Date(2014,3,9)) && (CreatedDate <= DATE(2014,11,2))) ||
((CreatedDate >= Date(2015,3,8)) && (CreatedDate <= DATE(2015,11,1)))
,(4/24),(5/24))

(Daylight Savings Time is determined by the Congress of the US….so these dates are subject to change.  Here’s the site I used to determine the dates: http://aa.usno.navy.mil/faq/docs/daylight_time.php

The reason this only works for the eastern time zone is the 4/24 and 5/24 in the last line of the formula.  If you need it to work for the Central time zone that last line would look like this:

,(5/24),(6/24))

Now that you have your offset field you’ll modify the first formula to look like this:

VALUE(MID(TEXT(CreatedDate – DST_Offset__c),12,2))

If the call came in at 9am, the field is “9”.  If the call came in at 3pm, the field is “15”.

Now that you have the hour, you can create summary reports and snazzy graphs to impress management!

Happy graphing!

Advertisement