Calculating Pay based on time of day hrs worked
 

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


--
scarlett1
------------------------------------------------------------------------
scarlett1's Profile: http://www.excelforum.com/member.php...o&userid=17495
View this thread: http://www.excelforum.com/showthread...hreadid=384375

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


--
agarwaldvk
------------------------------------------------------------------------
agarwaldvk's Profile: http://www.excelforum.com/member.php...o&userid=11345
View this thread: http://www.excelforum.com/showthread...hreadid=384375

thanks,

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

Thanks


--
scarlett1
------------------------------------------------------------------------
scarlett1's Profile: http://www.excelforum.com/member.php...o&userid=17495
View this thread: http://www.excelforum.com/showthread...hreadid=384375

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


--
scarlett1
------------------------------------------------------------------------
scarlett1's Profile: http://www.excelforum.com/member.php...o&userid=17495
View this thread: http://www.excelforum.com/showthread...hreadid=384375

Scarlett

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


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

If this is the case then apply the day or the night rate as applicable
(you can check which one applies by checking if the hour component of
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 to
determine the hours to which the day and the night rates apply. You do
this (in your example where your driver works between 5am and 8pm) by
substracting the hour component of 5am from the hour component of 6pm
and applying the day rate to these many number of hours. You then apply
the night rates to the number of hours arrived at by substracting the
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 you
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 that
wouldn't stop me from helping you out!


Best regards



Deepak Agarwal


--
agarwaldvk
------------------------------------------------------------------------
agarwaldvk's Profile: http://www.excelforum.com/member.php...o&userid=11345
View this thread: http://www.excelforum.com/showthread...hreadid=384375