Date and time calculations in Dashpivot Web allow you to automatically calculate the difference between two dates or times using formula fields. This is commonly used to measure durations, track delays, or calculate time intervals directly within a form. Date and time calculations are configured during template editing and are calculated when the form is completed.
Video Walkthrough
Prerequisites
Before you begin make sure you have the following:
Plan: Standard
Permission: Org Controller or Project Controller
Skills Required: Dashpivot
Device Type: Computer
Subscription: Dashpivot
Step-by-Step Instructions
Open the template you want to edit in the Template Editor.
Add the required Date, Date Time, or Date – Timezone Aware fields to the template.
Drag a Formula field into the template.
Select the Formula field to open the formula editor.
Enter one of the following formulas, depending on the calculation required:
Date and Time Calculations
To calculate the number of days between 2 date cells (A1 and B1), use the DATEDIF function:
=DATEDIF(A1,B1)
To calculate the number of hours between 2 time cells (A1 and B1), use the TIMEDIF function"
=TIMEDIF(A1,B1)
Both the DATEDIF & TIMEDIF functions will always return a positive number.
For formulas that cross-reference multiple tables:
Dates are stored numerically and need to be surrounded by
TEXT()to show a date format e.g.=TEXT(Table1!A1,"DD/MM/YYYY")
=TEXT(Table1!A2,"MM/DD/YYYY")Time is also stored numerically and will need to be surrounded by
TEXT()to show a time format e.g.=TEXT(Table1!B1-Table1!A1,"H:MM")
Overtime Calculations
To calculate the ordinary hours only (if more than 8 hours, just display 8). This formulas includes a break (C1).
=IF(TIMEDIF(A1,B1)-C1>8,8,TIMEDIF(A1,B1)-C1)
Then to calculate the overtime hours only (ie. anything above 8 hours):
=IF(TIMEDIF(A1,B1)-C1>8,TIMEDIF(A1,B1)-C1-8,0)
Replace the field names in the formula with the relevant date or time fields from your template.
Save the formula so the calculation is applied when the form is completed.
Notes and Tips
Date and time calculations use the DATEDIFF formula.
The unit parameter controls whether the result is returned in days, hours, or minutes.
Formula results update automatically when referenced date or time fields change.