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!

Advertisements

6 Responses to Counting Case/Leads Per Hour

  1. Wendy says:

    Thanks for providing this solution. I was receiving errors and had to change a couple things. First, I had to use the DATEVALUE function to convert the CreatedDate to a date. I also had to change the where the DST offset was being subtracted in the hour forumula. Here is what I ended up with.

    DST Offset formula
    IF(
    ((DATEVALUE(CreatedDate) >= DATE(2007,3,11)) && (DATEVALUE(CreatedDate) = DATE(2008,3,9)) && (DATEVALUE(CreatedDate) = DATE(2009,3,8)) && (DATEVALUE(CreatedDate) = DATE(2010,3,14)) && (DATEVALUE(CreatedDate) = DATE(2011,3,13)) && (DATEVALUE(CreatedDate) = DATE(2012,3,11)) && (DATEVALUE(CreatedDate) = DATE(2013,3,10)) && (DATEVALUE(CreatedDate) = DATE(2014,3,9)) && (DATEVALUE(CreatedDate) = DATE(2015,3,8)) && (DATEVALUE(CreatedDate) <= DATE(2015,11,1)))
    ,5,6)

    Created Hour formula
    VALUE(MID(TEXT(CreatedDate),12,2)) – DST_Offset__c

  2. Wendy says:

    Oops. the Created hour formula is wrong. It should be how you posted it. I messed up the if else values in the offset formula. So really the only thing I had to change was adding the DATEVALUE function.

  3. Damon says:

    Thanks for this, I’m having a little trouble though, the formulas all work well, but only when displayed on a record? when i try to report on them i get #Error instead of the value, do you know why this would happen?

    my formulas are,
    IF(
    (( DATEVALUE(CreatedDate) >= DATE(2011,4,3)) && (DATEVALUE(CreatedDate) = DATE(2012,4,1)) && (DATEVALUE(CreatedDate) = DATE(2013,4,7)) && (DATEVALUE(CreatedDate) = DATE(2014,4,6)) && (DATEVALUE(CreatedDate) = DATE(2015,4,5)) && (DATEVALUE(CreatedDate) <= DATE(2015,9,27)))
    ,0,1)

    and
    VALUE(MID(TEXT(CreatedDate + 0.5),12,2)) + DaylightSavings__c

    thanks
    damon

  4. Chris says:

    Thanks Amber!

    As a public service, here are the remaining Daylight Savings Time date ranges for the next 10 years…

    ((CreatedDate >= Date(2016,3,13)) && (CreatedDate = Date(2017,3,12)) && (CreatedDate = Date(2018,3,11)) && (CreatedDate = Date(2019,3,10)) && (CreatedDate = Date(2020,3,8)) && (CreatedDate = Date(2021,3,14)) && (CreatedDate = Date(2022,3,13)) && (CreatedDate <= DATE(2022,11,6)))

    • Chris says:

      Okay, WordPress seems to be doing something kooky when I paste in the formula…it removed all my November conditions.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: