Excel Magic Trick 1206: Day & Night Shift Time Calculations: Add Total Hours Single Cell Formula
43.5 هزار بار بازدید -
9 سال پیش
-
Download Excel Start File:
Download Excel Start File: https://excelisfun.net/files/EMT1206-...
Download File: https://excelisfun.net
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:
1. (00:10) Problem Setup
2. (01:00) Time Values in Excel are decimals between 0 and 1 that represent the proportion of a 24 hour day.
3. (02:13) Basic Time Calculation: End Time – Start Time
4. (03:11) IF formula for Day & Night Shift Time Calculations
5. (03:55) MOD function for Day & Night Shift Time Calculations
6. (05:00) Explanation of MOD algorithm: n-d*INT(d/n)
7. (06:50) Single Cell Array Formula for adding total hours for week using MOD function, Array operations and SUMPRODUCT function
Download File: https://excelisfun.net
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:
1. (00:10) Problem Setup
2. (01:00) Time Values in Excel are decimals between 0 and 1 that represent the proportion of a 24 hour day.
3. (02:13) Basic Time Calculation: End Time – Start Time
4. (03:11) IF formula for Day & Night Shift Time Calculations
5. (03:55) MOD function for Day & Night Shift Time Calculations
6. (05:00) Explanation of MOD algorithm: n-d*INT(d/n)
7. (06:50) Single Cell Array Formula for adding total hours for week using MOD function, Array operations and SUMPRODUCT function
9 سال پیش
در تاریخ 1394/04/25 منتشر شده
است.
43,594
بـار بازدید شده