Case Report grouped by Hour of Day

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

TEXT(
     CASE(
          VALUE(LEFT(RIGHT(text(CreatedDate),FIND(" ", TEXT( CreatedDate ))-2),2)), 
          00,19, 
          01,20, 
          02,21, 
          03,22, 
          04,23, 
          05,00, 
          06,01, 
          07,02, 
          08,03, 
          09,04, 
          10,05, 
          11,06, 
          12,07, 
          13,08, 
          14,09, 
          15,10, 
          16,11, 
          17,12, 
          18,13, 
          19,14, 
          20,15, 
          21,16, 
          22,17, 
          23,18, 
          00
      )
 )

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

value(right(left(text(CreatedDate),find(":",Text(CreatedDate))-1),2))

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

IF( 
     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

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

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