Sunday 6 November 2011

How to create a "Past 24 hours" flexible report

Sometimes as a 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 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!

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


  1. Thank you!! This was crazy helpful to me. Any updates since 2011 on this topic? I need to have a report run each day since the last time the report was run, but on mondays they will run it for date/time greater than when it was run on friday. I would love to use your scheduled every 24 hours idea, but there are 26 versions of the report that need to run each day and that takes up too many time slots in the schedule.

  2. I'm looking to reference the "Created by" date on an opportunity. Do you have any suggestions?

  3. Hi Dani -- I just used this method on the opportunity object instead of the Account object. Method and order is the same, you would just start by creating your custom field on the opportunity object instead of the account object.

  4. This comment has been removed by the author.

  5. Very helpful. Thanks Champ for sharing this

  6. how can I tweak this to show leads that came in over the weekend?

  7. Credit to West Indies, they played outstanding Ray Ban Glasses cricket. Ray Ban Outlet He (Dhananjaya) batted Coach Outlet Store at No.3 in the last 4 5 series and Coach Outlet he played some outstanding knocks. I feel we just want to play Yeezy Discount him. Now it was time for the third nephew, who was standing still and forlorn. "And what have you?" asked his rich uncle. "I gave my coin to a mission," he replied, "leaving only one farthing.

    AbstractUnstructured Coach Handbags Clearance grid ocean models are advantageous for simulating Yeezy Boost 350 the coastal ocean and river estuary plume systems. However, unstructured grid models tend to be diffusive and/or computationally expensive, which limits their applicability to real life problems. In this New Jordan Shoes 2020 paper, we describe a novel discontinuous Galerkin (DG) finite element discretization for the hydrostatic equations.

  8. CBO ERP Ltd provides a best pharma distribution billing software will also help to create reports for each aspect of distribution such as:
    • Register (Brief/Summary)
    • Sales & Purchase Register
    • Stock Transfer Issue Register
    • Sales Return
    • Purchase Return
    • Stock Report
    • C & F Wise Stock Consolidated
    • Stock Ledger A.R NO (Item/Batch Wise)
    • Cost Sheet.
    • Material receipt generation

    The software for pharma distributors provides a fast and foolproof platform to collect, manage, and improve sales data from different distributors and retailers located nationwide.