Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TIMESHEET
I am currently attempting to work out the flexi time earned and taken in a
week. The problem i am having is that if some one is taking flexi they would have had to work more than 37:00 hours a week, so if they haven't the flexi is a minus time. I need help with adding to times togeather in the format [h]:mm only if two conditions are met which are that an "F" needs to placed in a cell to confirm flexi being taken and one of the cells that need to be added must be greater the 0:00. If flexi has been earned by working over 37:00 hours and not taken then it must role over to the following week, but if flexi hours have not been earned (i.e less than 37:00 hours worked, so the flexi is a minus time) than i need to clear the cell insted of rolling it over to the next week. These are the attempts that i have made with no luck; =IF(AND(E10="F",H400),H36+H40,H40="") =IF(E10="F",H36+H40,TEXT(H40,"-[h]:mm")) =IF(E10:J10="F",TEXT(H36+H40,"[h]:mm"),TEXT(H40+I40,"[h]:mm")) Any guidence would be greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TIMESHEET
No negative flexi if contractual hours not completed, lucky!
I'd keep it simple have two columns for start and finish time. 3rd column would be finish time less start ie 17-9 (&less contractual hours per day) and that'd give you extra flexi per day and then just subtotal that for the week. Copy & paste that into the next week with a reference back so if (wk1<0,0,wk1) where wk1 would be the previous subtotal doug "Zaf" wrote: I am currently attempting to work out the flexi time earned and taken in a week. The problem i am having is that if some one is taking flexi they would have had to work more than 37:00 hours a week, so if they haven't the flexi is a minus time. I need help with adding to times togeather in the format [h]:mm only if two conditions are met which are that an "F" needs to placed in a cell to confirm flexi being taken and one of the cells that need to be added must be greater the 0:00. If flexi has been earned by working over 37:00 hours and not taken then it must role over to the following week, but if flexi hours have not been earned (i.e less than 37:00 hours worked, so the flexi is a minus time) than i need to clear the cell insted of rolling it over to the next week. These are the attempts that i have made with no luck; =IF(AND(E10="F",H400),H36+H40,H40="") =IF(E10="F",H36+H40,TEXT(H40,"-[h]:mm")) =IF(E10:J10="F",TEXT(H36+H40,"[h]:mm"),TEXT(H40+I40,"[h]:mm")) Any guidence would be greatly appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TIMESHEET
Thanks for the reply, but unfortunately staff often leave for visits and
therfore have multiple in and out times and i made a mistake in the posting if there is a negative time at the end of the week (if worked less than 37:00) than that also needs to be bought forward to the net week in order for the employee to make up the time and then the cell needs to be cleared. I would appreciate it if you could write an example formulae, so i can adapt to my needs. Kind Regards Zaf "DReid" wrote: No negative flexi if contractual hours not completed, lucky! I'd keep it simple have two columns for start and finish time. 3rd column would be finish time less start ie 17-9 (&less contractual hours per day) and that'd give you extra flexi per day and then just subtotal that for the week. Copy & paste that into the next week with a reference back so if (wk1<0,0,wk1) where wk1 would be the previous subtotal doug "Zaf" wrote: I am currently attempting to work out the flexi time earned and taken in a week. The problem i am having is that if some one is taking flexi they would have had to work more than 37:00 hours a week, so if they haven't the flexi is a minus time. I need help with adding to times togeather in the format [h]:mm only if two conditions are met which are that an "F" needs to placed in a cell to confirm flexi being taken and one of the cells that need to be added must be greater the 0:00. If flexi has been earned by working over 37:00 hours and not taken then it must role over to the following week, but if flexi hours have not been earned (i.e less than 37:00 hours worked, so the flexi is a minus time) than i need to clear the cell insted of rolling it over to the next week. These are the attempts that i have made with no luck; =IF(AND(E10="F",H400),H36+H40,H40="") =IF(E10="F",H36+H40,TEXT(H40,"-[h]:mm")) =IF(E10:J10="F",TEXT(H36+H40,"[h]:mm"),TEXT(H40+I40,"[h]:mm")) Any guidence would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with timesheet please | Excel Worksheet Functions | |||
timesheet with OT | New Users to Excel | |||
Timesheet help | Excel Worksheet Functions | |||
Timesheet | Excel Worksheet Functions | |||
timesheet | Excel Worksheet Functions |