Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating a rate for elapsed time? | Excel Discussion (Misc queries) | |||
I need help with a formula calculating time | Excel Discussion (Misc queries) | |||
Calculating tvl time in Excel 2000 Rev.9.2720 | Excel Worksheet Functions | |||
Calculating time difference | Excel Discussion (Misc queries) | |||
Calculating effective time from start/end date+time | Excel Worksheet Functions |