Filetype Excel XLSX | Posted on 18 Aug 2022 | 4 years ago
Authentication
digital resources
369x Filetype XLSX File size 0.76 MB Source: people.highline.edu
Sheet 1: Cover
| Excel & Business Math 31 | ||||||||||||||||||||||||||||||||||||||||||||||||
| Night Shift: Payroll Time Sheet Hours Worked Formula | ||||||||||||||||||||||||||||||||||||||||||||||||
| Topics: | ||||||||||||||||||||||||||||||||||||||||||||||||
| Day Shift or Night Shift? | ||||||||||||||||||||||||||||||||||||||||||||||||
| Negative Times Cause Error | ||||||||||||||||||||||||||||||||||||||||||||||||
| Night Shift Formula #1: IF Function | ||||||||||||||||||||||||||||||||||||||||||||||||
| Night Shift Formula #2: Boolean Formula | ||||||||||||||||||||||||||||||||||||||||||||||||
| Night Shift Formula #3: MOD Function | ||||||||||||||||||||||||||||||||||||||||||||||||
| 2 Awesome Homework Problems | ||||||||||||||||||||||||||||||||||||||||||||||||
| Fundamental Problem is that when Time Out is a smaller decimal than Time In, our "Hours Worked Formula" (Time Out - Time In)*24 does not work | ||||||||||
| Employee | Time In | Time Out | Time In as Decimal | Time Out as Decimal | ||||||
| Aasmussen, Kenya | 01:00 PM | 08:00 PM | 01:00 PM | 08:00 PM | ** Ctrl + Shift + ` or Ctrl + Shift + ~ = Apply General Number Format | |||||
| Alaniz, Vergie | 07:00 PM | 03:00 AM | 07:00 PM | 03:00 AM | ||||||
| Negative Times | When Time Out is | In Excel Math Formula: | ||||||||
| Not Allowed | Smaller Than Time In, | TRUE = 1 | ||||||||
| We Get a Negative | FALSE = 0 | |||||||||
| Employee | Time In | Time Out | In Our Head we can Make Calculation | Later Time - Earlier Time | Later Time - Earlier Time as decimal | Night Shift Time with IF | Logical Formula to check if Time Out < Time In | Night Shift Time with Boolean | Night Shift Time with MOD | MOD Function Algorithm |
| Aasmussen, Kenya | 01:00 PM | 08:00 PM | 7 Hours | |||||||
| Alaniz, Vergie | 07:00 PM | 03:00 AM | 8 Hours | |||||||
| Formula in cell E11: | Formula in cell F11: | Formula #01 in cell G11: | Formula in cell H11: | Formula #02 in cell I11: | Formula #03 in cell J11: | Formula in cell K11: | ||||
| Algorithm for how MOD Functions Calculates: | ||||||||||
| MOD(n, d) = n - d*INT(n/d) | ||||||||||
| Explanation here: | ||||||||||
| Excel Magic Trick 1206: Day & Night Shift Time Calculations: Add Total Hours Single Cell Formula | ||||||||||
| https://www.youtube.com/watch?v=VcgSkLpgo6o |
| Fundamental Problem is that when Time Out is a smaller decimal than Time In, our "Hours Worked Formula" (Time Out - Time In)*24 does not work | ||||||||||
| Employee | Time In | Time Out | Time In as Decimal | Time Out as Decimal | ||||||
| Aasmussen, Kenya | 01:00 PM | 08:00 PM | 0.541666666666667 | 0.833333333333333 | ** Ctrl + Shift + ` or Ctrl + Shift + ~ = Apply General Number Format | |||||
| Alaniz, Vergie | 07:00 PM | 03:00 AM | 0.791666666666667 | 0.125 | ||||||
| Negative Times | When Time Out is | In Excel Math Formula: | ||||||||
| Not Allowed | Smaller Than Time In, | TRUE = 1 | ||||||||
| We Get a Negative | FALSE = 0 | |||||||||
| Employee | Time In | Time Out | In Our Head we can Make Calculation | Later Time - Earlier Time | Later Time - Earlier Time as decimal | Night Shift Time with IF | Logical Formula to check if Time Out < Time In | Night Shift Time with Boolean | Night Shift Time with MOD | MOD Function Algorithm |
| Aasmussen, Kenya | 01:00 PM | 08:00 PM | 7 Hours | 07:00 AM | 0.291666666666667 | 0.291666666666667 | 0 | 0.291666666666667 | 0.291666666666667 | |
| Alaniz, Vergie | 07:00 PM | 03:00 AM | 8 Hours | 08:00 AM | -0.666666666666667 | 0.333333333333333 | 1 | 0.333333333333333 | 0.333333333333333 | |
| Formula in cell E11: | Formula in cell F11: | Formula #01 in cell G11: | Formula in cell H11: | Formula #02 in cell I11: | Formula #03 in cell J11: | Formula in cell K11: | ||||
| =C11-B11 | =C11-B11 | =IF(C11<B11,C11-B11+1,C11-B11) | =C11<B11 | =C11-B11+(C11<B11) | =MOD(C11-B11,1) | |||||
| Algorithm for how MOD Functions Calculates: | ||||||||||
| MOD(n, d) = n - d*INT(n/d) | ||||||||||
| Explanation here: | ||||||||||
| Excel Magic Trick 1206: Day & Night Shift Time Calculations: Add Total Hours Single Cell Formula | ||||||||||
| https://www.youtube.com/watch?v=VcgSkLpgo6o |
no reviews yet
Please Login to review.
