I need to create a report that shows the Case high traffic trends for each hour of the day for the current year. Basically, the customer wants to see a report for the current year of cases opened, grouped by the hour in which it was opened. So then they can see there busiest times of day, on average for the year.

For instance, if the case was opened at 11:30 am, then it would fall in the 11:00 -12:00 group.

I have a few ideas of how to accomplish this, I just want to make sure I am going about it the correct way.

Possibilities include

  1. Create a report and/or dashboard purely in Apex and VF. Seems fairly straight forward to create, just want to make sure I need to go fully custom before I decide to go that route.

  2. Use a formula field that looks at the Date/Time opened field and pulls just the time portion and using a CASE statement populates the appropriate time interval to the field. Use standard reporting on that field.

  3. Use some scheduled/batch apex or a trigger to populate the time interval field using the Date/Time Opened field, and then use standard reporting on that field.

I am fairly confident that any one of these ways would work, I just want to make sure I'm doing it in the most efficient way and I am not reinventing the wheel. So any thoughts and or tips from people that have implemented something similar would be much appreciated.

  • I am looking to do exactly this as well, but can't follow what you are doing. I am a SF noobie, so I kind of need a step by step as to how to do this. Would you be so kind as to help me out? Thanks! Chris
    – user5276
    Commented Jan 13, 2014 at 22:06

2 Answers 2


I was able to accomplish this using just a couple of formula fields. I likely could have done it with just 1 formula field, but for readability sake, I split it into 2 fields. They are both formula fields of type text.

'Case Opened Time' Field Criteria

          VALUE(LEFT(RIGHT(text(CreatedDate),FIND(" ", TEXT( CreatedDate ))-2),2)), 

NOTE: This is set up to convert the time to EST. If you do not want to convert to EST and leave it in GMT, then you would only need the formula to read


This returns just the number of the hour. For example 1:35 pm -> 13, 8:45 pm > 20, 3:15 am -> 3.

So then I created another formula field to make this more readable for the reports. It really just put the time into military format and created a range for that time.

'Case Opened Time (Formatted)' field Criteria

     LEN( Case_Opened_Time__c ) = 1, 
          '0' + Case_Opened_Time__c + ':00 - 0' + Case_Opened_Time__c + ':59',
          Case_Opened_Time__c + ':00 - ' + Case_Opened_Time__c + ':59'

All this does is return the hour range that the case was created in. For example 1:35 pm -> 13:00 - 13:59, 8:45 pm > 20:00 - 20:59, 3:15 am -> 03:00 - 03:59

Now that I had the formula field that gave me this time range, I was able to simply set up a standard summary report that showed the number of cases for the year, by the hour of day, that the case was opened. Here is a shot of the chart for the dashboard

enter image description here

Hopefully this helps someone out that wants to do something similar to this.

  • This is awesome. Thanks! I wonder if there is a similar solution to group dates by the week they fall in.
    – Richard N
    Commented Mar 19, 2014 at 16:11
  • 2
    There is simpler way to return hour from date/time field help.salesforce.com/articleView?id=000005369&type=1 MID( TEXT( CreatedDate - 5/24 ), 12, 2) )
    – sfdc
    Commented Mar 16, 2017 at 18:02
  • how do I create a formula field in lightning. Its grayed out for me? Commented Apr 16, 2021 at 17:23

I vote for option #2: using a formula field to extract the hour. I prefer this method because:

  1. It is completely declarative
  2. It is the simplest method
  • I agree this is likely the way to go. I'm going to give it a shot and I will post back what I come up with once I get working Commented Sep 25, 2013 at 19:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .