A guide on how to create a target formula where the target is increasing every hour.
Table of contents
How does it work?
You can create a target value formula where the value increases every hour based on the relevant time of the day. Hourly targets can be a fun way to motivate your teams and give everyone a more immediate sense of achievement because they get to reach smaller milestones.
To create the hourly target formula, you need to use a basic IF statement. Since the Visual editor (BETA) doesn't support IF statements yet, you will have to use the Advanced formula editor.
Hourly target – formula example
Say your team is keeping track of their daily performance, and the goal is to prepare 40 orders on a given day (or make 40 calls, or any other activity). Everyone's working for 8 hours a day. It means that on average, they should prepare 5 orders per hour. Once you know how much work everyone should do per hour, you have all the necessary information to build the formula.
- Go to Formulas > + New formula.
- Click the Switch to advanced editor button in the top-right corner.
- Copy and paste the following formula. Make sure to add your own targets and working hours.
The formula below expects an hourly target of 5. The working hours are between 9 am and 5 pm (9-17), and the total workday amounts to 8 hours. With this formula, the target value of the first hour will be 5, the following hour it will be 10, then 15, and so on.
( 5* ( IF(Hour(Now())>9, IF(Hour(Now())<=17, Hour(Now())-8, 0), 0) ) )
Once you save the formula, add it to a dashboard widget and set the time period to Today. We would use the number box or speedometer because they can show two formulas at once (actual and target value). Here's how the formula would look on the widget between 2 and 3 pm (the target value is 30).
Tip – Add custom conditional colors
Define your success criteria and add custom conditional colors right on the widget to get a better visual understanding of your progress.
Was this article helpful?
Please leave a comment to help us improve.