Calculating Pay based on time of day hrs worked

[scarlett1]

Hi, Im setting up a spreadsheet for payroll which so far has start time,
end time, total hrs worked and time worked minus break time.

I need to set up a function to look at the working times and if between
6am and 6pm calculate number of hrs worked at day rate, then if between
6pm - 6am calculate number of hrs worked at night rate.

any ideas?

thanks

 

[agarwaldvk]

Scarlett1

You might want to use the hour function to extract the hour of the
start time and/or finish times depending upon your requirements.

Then check if this start time is before or after 6 pm (you might again
want to extract the hour of the 6pm) before you perform this check.


Best regards


Deepak Agarwal

 

[scarlett1]

thanks,

could you give me a bit more detail on how i check? If a driver work
from 5am to 8pm, i need to pay him night rate for the hours worke
outside 6am to 6pm.

Thank

 

[scarlett1]

can someone help with the hour function in excel? I need to work ou
number of hours worked between 6am and 6pm and the number of hour
worked between 6pm and 6am. I think I need to use an if function but I
not 100% sure what it should be

 

[agarwaldvk]

Scarlett

I haven't tried this myself and it is a suggestion only! You might hav
to give it a try!


In my earlier post, I have indicated how you can extract the hou
component from both the start and finish times. Once you have don
that, you will need to check if *BOTH* of these are before 6 pm O
*BOTH* of these are after 6 pm.

If this is the case then apply the day or the night rate as applicabl
(you can check which one applies by checking if the hour component o
either one of them is before or after 6pmm) for all the hours worked.

If not, then you know that both of these rates apply. You will need t
determine the hours to which the day and the night rates apply. You d
this (in your example where your driver works between 5am and 8pm) b
substracting the hour component of 5am from the hour component of 6p
and applying the day rate to these many number of hours. You then appl
the night rates to the number of hours arrived at by substracting th
hour component of 6pm from the hour component of the finish time
Finally take the sum of the two.

Yes, this would be a case of nested if's.

My recommendation is that you first give it a go on your own. If yo
have persistent problems, feel free to seek further assistance.

I will be more than happy to help!

At this particular point in time, I am in a bit of hole myself but tha
wouldn't stop me from helping you out!


Best regards



Deepak Agarwa