Dashpivot Web allows you to calculate the difference between two Date cells or two Time cells within the same table row using formula functions. The DATEDIF function calculates the number of days between two Date cells, while the TIMEDIF function calculates the number of hours between two Time cells. This article explains how to configure these formulas in a table row.
Prerequisites
Before you begin make sure you have the following:
Plan: Standard, Pro, Premium, or Platinum
Permission: Org Controller (Home Folder) or Org Controller, Project Controller, Team Controller (Team Folder)
Skills Required: Dashpivot, Excel formula knowledge
Device Type: computer
Subscription: Dashpivot Web
Step-by-Step Instructions
Log in to Dashpivot Web and open the relevant template in the Template Editor.
Ensure your template contains a table with either two Date cells or two Time cells in the same row.
Add a new column to the table and select Formula as the table cell type.
In the Formula field, enter the required function:
To calculate the number of days between 2 Date cells, use the DATEDIF function:
=DATEDIF(Date1, Date2)
Using cells:
=DATEDIF(A1, B1)
To calculate the number of hours between 2 Time cells, use the TIMEDIF function:
=TIMEDIF(Time1, Time2)
Using cells:
=TIMEDIF(A1, B1)
Click Save to apply the formula.
Enter values into the Date or Time cells to confirm the formula calculates correctly.
Notes
Both the
DATEDIFandTIMEDIFfunctions will always return a positive number.The formula applies only to cells within the same table row.
Example
Suppose you are creating a form to calculate the total hours an employee has worked in a day.
In a Default table:
Use the formula in display cell C1:
=TIMEDIF(A1, B1)
Time In | Time Out | Total Hours |
(A1 - time field) | (B1 - time field) | =TIMEDIF(A1, B1) |
Default tables do not have a fixed number of rows when creating a template. Formulas reference columns, so each time you add a new row while filling out the form, the same formula applies automatically to each cell in that column.
In a Prefilled table:
Use the formula in the display cells in Column C and adjust accordingly:
=TIMEDIF(A1, B1)
Time In | Time Out | Total Hours |
(A1 - time field) | (B1 - time field) | =TIMEDIF(A1, B1) |
(A2 - time field) | (B2 - time field) | =TIMEDIF(A2, B2) |
(A3 - time field) | (B3 - time field) | =TIMEDIF(A3, B3) |
Or, use this format and apply the formula in Row C:
=TIMEDIF(B1, C1)
| Column B | Column C |
Time In | (B1 - time field) | (C1 - time field) |
Time Out | (B2 - time field) | (C2 - time field) |
Total Hours | =TIMEDIF(B1, B2) | =TIMEDIF(C1, C2) |
Calculations can occur across all rows and columns within a Prefilled table. You can reference any cell depending on your chosen layout.
Notes
Both the
DATEDIFandTIMEDIFfunctions always return a positive number.Calculations apply only within the same table unless referencing specific cells in a Prefilled table.
Default tables apply formulas automatically to each new row added during form completion.