Excel Magic Trick 1206

Day & Night Shift Time Calculations: Add Total Hours Single Cell Formula


See how to create a single cell formula that calculates the total hours worked for the week given a column of start times and a column of end times:
(00:10) Problem Setup
(01:00) Time Values in Excel are decimals between 0 and 1 that represent the proportion of a 24 hour day.
(02:13) Basic Time Calculation: End Time – Start Time
(03:11) IF formula for Day & Night Shift Time Calculations
(03:55) MOD function for Day & Night Shift Time Calculations
(05:00) Explanation of MOD algorithm: n-d*INT(d/n)
(06:50) Single Cell Array Formula for adding total hours for week using MOD function, Array operations and SUMPRODUCT function


Be the first to comment

Leave a Reply