N
Neil Gerace
Hi,
Problem: employees get paid different hourly rates at different times
of the day. From 00:00 to 08:00 is $NIGHTRATE, from 08:00 to 17:00 is
$DAYRATE, from 17:00 to 21:00 is $EVENINGRATE and from 21:00 to 24:00
is $NIGHTRATE again. As well as that there is an hourly surcharge of
$LOADING for working on Saturday or Sunday.
So someone working from 7:30 to 9:00 (1.5 hours) on a Sunday would get
paid 0.5 * $NIGHTRATE + 1 x $DAYRATE + 1.5 * $LOADING.
I want to do a spreadsheet that will calculate how many hours any
employee works during each of the four different periods of the day,
in order to work out their pay. The only input from the user should be
the date (to determine whether $LOADING applies) and the hours worked.
There is only 1 shift per worker per day, and shifts never span across
midnight.
Is there anything in Excel short of a mass of logical functions that
will split these timespans out easily and work this out for me?
Problem: employees get paid different hourly rates at different times
of the day. From 00:00 to 08:00 is $NIGHTRATE, from 08:00 to 17:00 is
$DAYRATE, from 17:00 to 21:00 is $EVENINGRATE and from 21:00 to 24:00
is $NIGHTRATE again. As well as that there is an hourly surcharge of
$LOADING for working on Saturday or Sunday.
So someone working from 7:30 to 9:00 (1.5 hours) on a Sunday would get
paid 0.5 * $NIGHTRATE + 1 x $DAYRATE + 1.5 * $LOADING.
I want to do a spreadsheet that will calculate how many hours any
employee works during each of the four different periods of the day,
in order to work out their pay. The only input from the user should be
the date (to determine whether $LOADING applies) and the hours worked.
There is only 1 shift per worker per day, and shifts never span across
midnight.
Is there anything in Excel short of a mass of logical functions that
will split these timespans out easily and work this out for me?