TIMESHEET

[Zaf]

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",H40>0),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.

 

[DReid]

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]

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

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

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",H40>0),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.