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)