Trying to come up with a formula that will calculate 3 pay rates that are based on the time of day, not the number of hours worked.
For example:
$10/hr for 7a-7p
$11/hr for 7p-12a
$12/hr for 12a-7a
If the person worked 2 hours from 9-11 am, they would get $11x2 hours
If the person worked 14 hours from 6 pm to 8 am the next day, they would get:
$10 for 6p-7p
$55 for 7p-12a
$84 for 12a-7a
$10 for 7a-8a
===
$159 total
Thanks for any advice.
I have formulas like D2-TIMEVALUE("18:00") but that doesn't take into account if the time period started after the start of Rate 2, and would return only the difference between the time and 18:00 instead of the start time, say at 18:30.
Bookmarks