securityman
07-23-2005, 07:05 PM
I'm back again, still working with time.
Monday
in 8:00
out 17:00
Tuesday
in 6:00
out 17:00
I want to calculate the time between the clock out time on Monday and the clock in time on Tuesday.
_________________________________________
Monday
in 8:00
out 17:00
Tuesday
in 0:00
out 0:00
Wednesday
in 8:00
out 17:00
Want to calculate the number of hours between clock out time on Monday and the clock in time on Wednesday.
Then what if you have 2 day off between Monday and Thursday?
I can do a formula for the first one (Mon to Tues), but if I try for the second one, I have to do 6 IF statements. Surely theres a better way.
I have a sheet that will take times off a time card and calculate the hours worked for each day. Then I want it to do the above in a cell between each day to show how many hours they were off between shifts. (Sheet 1)
I then coded a button to put the hours worked for each day into another sheet (Sheet 2). That was no problem. On this sheet it takes the last entry in the column and adds the preveious 7 entries to it and then subtracts it from 70.
That's the number of hours worked in the last 8 days minus 70. This leaves me with how many hour the person can work the next day.
When the person has 34 hours or more off calculated on Sheet 1, I need the hours to be reset to 70 on Sheet 2 and the time to start subtracting from there.
EX: last column in sheet 2 is 10. (10 hours left to work the next day)
If the person has 34 or more hours off from Sheet 1 then the blank cell under the 10 is reset to 70 and the calculation start over. It always has to add the present cell plus the previous seven cell and subtract it from 70 unless a cell has been reset to 70 then it has to start subtracting from there.
Clear as mud?
By the way if any one recognizes this it is for truck drivers who work a 70 hour / 8 day Rule.
Right now Sheet 2 is calculating the hours correctly until someone takes 34 or more hours off. It won't reset to 70. I have it highlight the cell in which the 70 hours have been used up and needs to be reset, but you have to look back to Sheet 1 to calculate when (or if) someone took 34 or more hours off.
The workbook has 13 sheets in it. 12 for the months and 1 for the calculation of the hours worked per day. I could e-mail a copy to anyone willing to try and help me so they could see what I have so far.
Thanks,
Monday
in 8:00
out 17:00
Tuesday
in 6:00
out 17:00
I want to calculate the time between the clock out time on Monday and the clock in time on Tuesday.
_________________________________________
Monday
in 8:00
out 17:00
Tuesday
in 0:00
out 0:00
Wednesday
in 8:00
out 17:00
Want to calculate the number of hours between clock out time on Monday and the clock in time on Wednesday.
Then what if you have 2 day off between Monday and Thursday?
I can do a formula for the first one (Mon to Tues), but if I try for the second one, I have to do 6 IF statements. Surely theres a better way.
I have a sheet that will take times off a time card and calculate the hours worked for each day. Then I want it to do the above in a cell between each day to show how many hours they were off between shifts. (Sheet 1)
I then coded a button to put the hours worked for each day into another sheet (Sheet 2). That was no problem. On this sheet it takes the last entry in the column and adds the preveious 7 entries to it and then subtracts it from 70.
That's the number of hours worked in the last 8 days minus 70. This leaves me with how many hour the person can work the next day.
When the person has 34 hours or more off calculated on Sheet 1, I need the hours to be reset to 70 on Sheet 2 and the time to start subtracting from there.
EX: last column in sheet 2 is 10. (10 hours left to work the next day)
If the person has 34 or more hours off from Sheet 1 then the blank cell under the 10 is reset to 70 and the calculation start over. It always has to add the present cell plus the previous seven cell and subtract it from 70 unless a cell has been reset to 70 then it has to start subtracting from there.
Clear as mud?
By the way if any one recognizes this it is for truck drivers who work a 70 hour / 8 day Rule.
Right now Sheet 2 is calculating the hours correctly until someone takes 34 or more hours off. It won't reset to 70. I have it highlight the cell in which the 70 hours have been used up and needs to be reset, but you have to look back to Sheet 1 to calculate when (or if) someone took 34 or more hours off.
The workbook has 13 sheets in it. 12 for the months and 1 for the calculation of the hours worked per day. I could e-mail a copy to anyone willing to try and help me so they could see what I have so far.
Thanks,