Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OVERTIME AND DOUBLETIME
I am trying be able to enter actual hours worked in a day and have it
calculate what is regular time and overtime with RT being 7:00am - 3:30pm (1/2hr lunch included) M-F anytime before or after is OT.. PLEASE HELP!!!! THANK YOU |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OVERTIME AND DOUBLETIME
Download the workbook below which should solve your problem
http://www.cpearson.com/excel/overtime.htm "E-QUIP" wrote: I am trying be able to enter actual hours worked in a day and have it calculate what is regular time and overtime with RT being 7:00am - 3:30pm (1/2hr lunch included) M-F anytime before or after is OT.. PLEASE HELP!!!! THANK YOU |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OVERTIME AND DOUBLETIME
how do i tell it my so called "custom hours" i want it to be able to p/u my
7:00am - 3:30pm being regular hours and anything before or after to be overtime??? "Mike" wrote: Download the workbook below which should solve your problem http://www.cpearson.com/excel/overtime.htm "E-QUIP" wrote: I am trying be able to enter actual hours worked in a day and have it calculate what is regular time and overtime with RT being 7:00am - 3:30pm (1/2hr lunch included) M-F anytime before or after is OT.. PLEASE HELP!!!! THANK YOU |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OVERTIME AND DOUBLETIME
Here are a few hints for a solution that breaks the task down to manageable
bits -- let's say the employee's time of arrival is stored in a cell named "ArriveTime", the time he/she left in "LeaveTime", and the beginning and end of regular working hours are stored in "RTstart" and "RTend" respectively. If someone arrives earlier than beginning of regular working hours (i.e. ArriveTime < RTstart), there's some overtime. We can use the IF worksheet function to figure out the number of overtime hours in early morning: = IF(ArriveTime<RTstart ; (RTstart-ArriveTime)*24 ; 0) If the condition "ArriveTime<RTstart" is met, this formula works out how many hours overtime that makes for in the morning (the factor 24 is necessary to convert the decimal days that Excel works with, to the hour count we'd like). If the condition isn't met, it returns the zero I've put near the end of the formula; zero morning overtime hours for those who arrive at regular time start (or later!). We can do a similar trick for those who stay to after the end of regular time: = IF(LeaveTimeRTend ; (LeaveTime-RTstart)*24 ; 0) Now, add the results of the two formulae I've written here, and you've got the amount of overtime hours for that employee that day. Now let's look at how many of the regular working hours the employee is present: If an employee arrives before regular hours, we've already worked out their overtime; their "chunk" of regular working hour that day still starts at the regular hours' start. For those arriving later, their regular hours start whenever they arrive. So, it's a whatever-comes-last-situation; regular hours or actual arrival time. We can use the MAX worksheet function to determine this: = MAX(ArriveTime ; RTstart) At the end of the day, the reverse applies: The chunk of regular hours ends a regular time end, or when the employee leaves, whichever comes first. So the formula is: = MIN(LeaveTime ; RTend) Now we can figure out how many work hours that employee put in within that regular time "window" by simply subtracting the result of the latter from the result of the former. This is just a basic "sketch" of a solution -- I haven't accounted for that half-hour lunchbreak you mentioned, and if someone is at work by midnight the method presented here will fail, but hopefully you've gleaned a few tips and ideas on how to make it work just like you want it. -- Can I run that on my trusty ol' Commodore 64...? ;-) "E-QUIP" skrev: I am trying be able to enter actual hours worked in a day and have it calculate what is regular time and overtime with RT being 7:00am - 3:30pm (1/2hr lunch included) M-F anytime before or after is OT.. PLEASE HELP!!!! THANK YOU |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OVERTIME AND DOUBLETIME
Is it possible for me to email my workorder to you for you to look at to help
me? "Peo" wrote: Here are a few hints for a solution that breaks the task down to manageable bits -- let's say the employee's time of arrival is stored in a cell named "ArriveTime", the time he/she left in "LeaveTime", and the beginning and end of regular working hours are stored in "RTstart" and "RTend" respectively. If someone arrives earlier than beginning of regular working hours (i.e. ArriveTime < RTstart), there's some overtime. We can use the IF worksheet function to figure out the number of overtime hours in early morning: = IF(ArriveTime<RTstart ; (RTstart-ArriveTime)*24 ; 0) If the condition "ArriveTime<RTstart" is met, this formula works out how many hours overtime that makes for in the morning (the factor 24 is necessary to convert the decimal days that Excel works with, to the hour count we'd like). If the condition isn't met, it returns the zero I've put near the end of the formula; zero morning overtime hours for those who arrive at regular time start (or later!). We can do a similar trick for those who stay to after the end of regular time: = IF(LeaveTimeRTend ; (LeaveTime-RTstart)*24 ; 0) Now, add the results of the two formulae I've written here, and you've got the amount of overtime hours for that employee that day. Now let's look at how many of the regular working hours the employee is present: If an employee arrives before regular hours, we've already worked out their overtime; their "chunk" of regular working hour that day still starts at the regular hours' start. For those arriving later, their regular hours start whenever they arrive. So, it's a whatever-comes-last-situation; regular hours or actual arrival time. We can use the MAX worksheet function to determine this: = MAX(ArriveTime ; RTstart) At the end of the day, the reverse applies: The chunk of regular hours ends a regular time end, or when the employee leaves, whichever comes first. So the formula is: = MIN(LeaveTime ; RTend) Now we can figure out how many work hours that employee put in within that regular time "window" by simply subtracting the result of the latter from the result of the former. This is just a basic "sketch" of a solution -- I haven't accounted for that half-hour lunchbreak you mentioned, and if someone is at work by midnight the method presented here will fail, but hopefully you've gleaned a few tips and ideas on how to make it work just like you want it. -- Can I run that on my trusty ol' Commodore 64...? ;-) "E-QUIP" skrev: I am trying be able to enter actual hours worked in a day and have it calculate what is regular time and overtime with RT being 7:00am - 3:30pm (1/2hr lunch included) M-F anytime before or after is OT.. PLEASE HELP!!!! THANK YOU |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|