Restrict by Time of Day

Some pinboards are able to select a subset of data based on the time within a day. This means you can use date selectors to select a large range and the time of day restriction to select a subset. For example if you enter a date range of "1-jan-2023 to 1-feb-2023 and timeofday=13" then only values from the 1pm to 2pm for each day will be used.

The timeofday consumes several types of parameters

• If a single value between 0 and 23 (inclusive) is provided, the function selects the single hour starting with the value given. timeofday=15 therefore selects all records betweem 3 and 4pm
• Two values provide the start and end hours. For example timeofday=9,11 selects records 9 to 10am AND 10 to 11am.

=Pinboard("product.sales.count.period",20,"1-jan-2022","1-jan-2023",,,"timeofday=14")
or
=Pinboard($O$1,$B$1,$D$1,$F$1,,,CONCAT("timeofday=",$A6))

Restrictions:

• Only whole hours are available
• Start Hour must be less than end hour. timeofday=11,8 is invalid.
• Times are only considered within a single day, you cannot cross midnight. For example, trying to select 5pm to 9am the next day is not possible. In this case enter the pinboard twice for two time ranges and add the values together. Ensure you adjust the date range selector as well.

  =Pinboard("product.sales.count.period",20,"1-jan-2022","1-jan-2023",,,"timeofday=17,24")
  +Pinboard("product.sales.count.period",20,"2-jan-2022","2-jan-2023",,,"timeofday=0,9")
  

Available on

• product.sales.count.period
• product.sales.revenue.period
• product.sales.linecount.period
• product.sales.revenue.notax.period
• product.sales.cost.period
• department.sales.count.period
• department.sales.revenue.period
• department.sales.linecount.period
• department.sales.revenue.notax.period
• sales.count.period
• sales.rawrevenue.total
• sales.rawrevenue.notax
• sales.rawrevenue.tax1
• sales.count.layby
• sales.count.parked

Worked Examples

Download .XLSX file Uses dayofweek restriction and also timeofday restriction to breakdown product/department sales by day and/or time
Download .XLSX file Uses dayofweek restriction on total sales