How to create a "Past 24 hours" flexible report

Sometimes as a Force.com developer you can be asked to complete tasks that on the surface seem simple, but can quickly prove beyond difficult due to the current limitations of the platform. Recently a client asked if it was possible to construct a report that listed all the Accounts that had been created in the past twenty four hours. They were looking to run this report at the end of their business day, which runs between 3pm one day to 3pm the following day.

Given this report criteria, lets take a look at the options available at a day level in reporting for a datetime field like created time:

As you can see, we are limited to specific datetime ranges (Current day, yesterday, this week, last FY etc.). While there are standard criteria values relating to the current day (00:00:00 to 23:59:59), there are no entries that relate to the current time in the system. Of course this time range can be defined manually in the "From" and "To" entry fields when using the "Custom" range option, but this means the report will only work for that specific time frame, essentially meaning a new unique report has to be created each day.

At this point, my options seemed to be:

1. Instruct the client to change the report criteria daily and run the report manually at 3pm (unreliable and time consuming).
2. Try to convince the client to change their business practice hours to fit the strict day by day Salesforce model (and find myself in an Force.com developer shaped hole in the wall).

Not wanting to propose either of these approaches, I pressed on, determined to find another way. I came to the realisation that while it is not possible to create a 24 hours report directly through manipulating criteria in the report builder, a custom formula field can be created on the account object that can identify if the created date field date/time value is within the last the past 24 hours. A report can then be created where results are filtered based on the value of this formula field.

To do this, first create a new text formula field on the Account object, giving it a suitable name such as "Account created past 24 hours". In the formula entry panel, enter the following formula:

IF ( NOW() - CreatedDate < 1 ,"TRUE", "FALSE" )

In formula fields, if you subtract one date/time from another, the result is returned in the form of a number. This number is the amount of days between the two expressed as a decimal (1 = 1 day, 3.5 = 3 days 12 hrs etc). The formula above calculates the decimal time difference between the current system time and the date and time the account object was created. If this value is less than or equal to 1, it follows that the account was created in the last 24 hours.

Formula fields cannot be defined as returning a simple boolean value, so instead the formula is set as a text field and the logic condition is encapsulated inside an if statement. If the condition is true (account created in past 24 hours) a TRUE text value will be returned, if the condition is false, FALSE will be returned. If preferred, this could be simplified to Y and N, or even a numerical formula field with values of 1 and 0.

After creating the formula field, include the field and the created date in an account view to ensure the field behaves as intended. The following screenshot was created today. Don't be fooled by the appearance of the created date field. Although it only displays a date string with no time, it is a date/time field. To see the date time value, simply click onto an account record and find the field on the account detail page.  

We can now leverage this formula field to create our past twenty four hours report. Simply create a new account report, and add a new filter where the value of our custom "Account created past 24 hours" formula field is equal to "TRUE". Make sure that the Range date criteria on the line above is set to "All Time"

Use the report builder to select all the detail fields you wish to include in the report, and save the new report.  Run the report, and you should see the details of all the accounts created in the past 24 hours. The client now has a report they can run at three o'clock that shows all the accounts created in the past 24 hours. Magic!

BUT!!!
This is still not an entirely suitable solution for our problem. The original request was for a report that could see the accounts that were created between 3pm on the current day and 3pm the previous day. If the client ran the report at any time other than exactly 3pm, the result would be skewed, and relevant account records could be potentially excluded from the report.

If this report were to be scheduled to be run every day at 3pm inside Salesforce, the same skewing would occur, as report scheduling simply indicates a preferred time. The following is extracted directly from Salesforce scheduling a report documentation

• The report runs within 30 minutes of the time you select for Preferred Start Time. For example, if you select 2:00 PM as your preferred start time, the report runs any time in between 2:00 PM and 2:29 PM, depending on how many other reports are scheduled at that time.

The formula needs to be adapted to cope with this potential skewing, so the report can be scheduled to run correctly. Instead of just comparing the created date field to the value of the current system date time (NOW()), the current system time needs to be rounded to last whole hour. There is no method or function within formula fields that allows for the manipulation of date/time fields, so in order to accomplish this we need to transform the two date time variables into a numerical forms that can be manipulated.

As illustrated in the initial draft of the formula above, the result of subtracting one date time value from another is always returned in a numerical form. Comparable numerical values of our date time variables can therefore be created by subtracting a common early base date/time value from both values. Fortunately, Salesforce provides a suitable offset date/time out of the box. The System.OriginDateTime variable is a global Salesforce variable, and is a date/time representation of the turn of the 20th century (1900-01-01 00:00:00).

So to convert the current system date time into a numerical form, use the following inside a formula field:

NOW() - ($System.OriginDateTime)

and to convert the account created date to a similar comparable value:

CreatedDate - ($System.OriginDateTime)

However we are still not finished, we need to round the numerical representation of the current system time to the nearest hour. Remember that all numerical representations of date/time comparison values represent the number of days between the two time-stamps as a decimal. To convert this value to be in hours instead of days, simply multiply the decimal value by 24. The date/time value can now be rounded down to the nearest hour by using the FLOOR function on the decimal number. The excess minutes have now been removed. Divide the resultant rounded number by 24 to convert the value back into a value based on days, and it can now effectively be used in the comparisson formula.

The final formula looks like this:

IF ((FLOOR ((NOW() - ($System.OriginDateTime)) * 24) / 24)  - 
(CreatedDate - ($System.OriginDateTime)) < 1, "TRUE", "FALSE" )

If you update your formula field to the new value shown above, and then run the report again, you will see that it the cut off point for account created dates has now been rounded to the last hour. So it doesn't matter if you run the report at 3:00 pm, 3:01 pm, 3:30 pm or 3:59pm, the result will always be the same.

So now the client has a report they can run  that shows them the account records created in the last twenty four hours, that only varies by the hour, not the minutes. The report is scheduled so every day at some time between 3pm and 3:30pm they receive a direct email report of all accounts created in their last business day.

So there you have it, happy reporting! This was a task that seemed simple on the surface, but quickly became very complex. An effective solution has been created by using alternative thinking and manipulation of other native Salesforce utilities. I am a firm believer that although Salesforce does not always provide functionality as required straight out of the box, there is always another way to accomplish business goals with the suite of tools they provide.

A big plus point of this solution is that it opens to door to solve numerous report time criteria challenges. This date/time manipulation formula field could be applied to any salesforce date/time field, not just created date and not just on the account object. The formula can also be adjusted to different timeframe scenarios (previous 48 hrs, past ten minutes, an hour either side).

As a bonus tip if you ever have to use this method, there can be scenarios where you have to account for future time values. In the created date example, all values of the comparison field ,created date, were guaranteed to be in the past. If you had date/time values you wished to analyse that potentially had values in the future, you have to add an extra clause to account for the negative values, like so:

IF ( 

AND( 

Custom_DateTime_Field__c - ($System.OriginDateTime) >= 
((FLOOR ((NOW() - ($System.OriginDateTime)) * 24) / 24) -1), 

Custom_DateTime_Field__c - ($System.OriginDateTime) < 
(FLOOR ((NOW() - ($System.OriginDateTime)) * 24) / 24) 

), "TRUE", "FALSE" .

)

If you have any questions about the formula field, or how to define a formula field for a particular timespan or scenario, get in touch!