Print this page

Formula - How to group a report by hour?

Knowledge Article Number 000005369
Description

You can show the breakdown of the report data by date/time field, and show this information in hourly increments.

NOTE: This formula can show the hours in GMT, or a specific timezone

Resolution

A Date/Time can be converted to Text, when doing so it always has the same length, so you can easily extract the hours.

Following formula will return the hour as a number in GMT:

VALUE( MID( TEXT( LastModifiedDate ), 12, 2) ) )

Following formula will return the hour as a number in GMT+2:

VALUE( MID( TEXT( LastModifiedDate + 2/24 ), 12, 2) ) )

Following formula will return the hour as a TEXT in GMT:

MID( TEXT( LastModifiedDate ), 12, 2) )

Following formula will return the hour as a TEXT in GMT+2:

MID( TEXT( LastModifiedDate + 2/24 ), 12, 2) )

Using the offset '+2/24' is for GMT+2, use '-5/24' for GMT-5.

See also:
Using Date and Date/Time Values in Formulas




promote demote