Calculating date/time differences allows you to determine the date/time remaining between two dates/times.
To start, navigate your cursor to the left tab and drag the table (Default or Prefilled). Set two cells are your date cells and make sure they're both set to as 'Date' fields. Set the third cell as your display or formula cell.
To calculate the number of days between 2 date cells, used 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)
Note: Both the DATEDIF and TIMEDIF functions will always return a positive number.
Examples:
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 the display cell C1:
=TIMEDIF(A1, B1)
Time In | Time Out | Total Hours |
(A1 - time field) | (B1 - time field) | =TIMEDIF(A1, B1) |
Default tables don't have a fixed number of rows when you're creating a template. Your formulas will reference columns, so each time you add a new row in filling out your form, the same formula applies 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 in this format, use the formula in the display cells in Row C and adjust accordingly:
=TIMEDIF(B1, C1)
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 & columns within the prefilled table. So whichever format you choose, you can reference any cell.