Hello, I am trying to figure out how to make a spreadsheet that can calculate my gross pay. I get a base pay along with zero or one of three shift differentials, depending on when I work. An example is below. What I have right now is a spreadsheet with my clock in and out times, the amount of regular hours, and columns for each of the three differentials. What formula would I use to calculate the time of my shift that is in that differential?
Differential times are as follows:
1 - weekdays 18:00-06:00
2 - weekends 06:00-18:00
3 - weekends 18:00-06:00
My spreadsheet right now looks something like this:
<tbody>
</tbody>
And it should look like this:
<tbody>
</tbody>
Differential times are as follows:
1 - weekdays 18:00-06:00
2 - weekends 06:00-18:00
3 - weekends 18:00-06:00
My spreadsheet right now looks something like this:
Date | Clock In | Clock Out | Total Hours | Regular Hours | Differential 1 Hours | Differential 2 Hours | Differential 3 Hours |
3/10/18 | 14:00 | 22:00 | 8 | 8 | |||
3/11/18 | 23:00 | 07:00 | 16 | 8 | |||
3/17/18 | 14:00 | 22:00 | 24 | 8 |
<tbody>
</tbody>
And it should look like this:
Date | Clock In | Clock Out | Total Hours | Regular Hours | Differential 1 Hours | Differential 2 Hours | Differential 3 Hours |
3/10/18 | 14:00 | 22:00 | 8 | 8 | 4 | 0 | 0 |
3/11/18 | 23:00 | 07:00 | 16 | 8 | 0 | 0 | 7 |
3/17/18 | 14:00 | 22:00 | 24 | 8 | 0 | 4 | 4 |
<tbody>
</tbody>