auto zero cell data on a function???

[Resi]

hi
wonder if you can help me?
I am (still) developing our staff timesheets. i have now been asked if it
would be possible to add a new function based on our new overtime policy. it
means that any overtime taken, should be taken within 2 months. so is anyone
able to figure out how i ask excel to delete overtime automatically so that
for example (here is when myself gets lost):

in May i do 2 hrs overtime.
by the policy i have time until end June to take this time off so excel
should zero the cell that tracks overtime in July BUT only for the hours
taken in May leaving the hours taken in June still to be used up.

does this make sense or have i managed to confuse you as well?

thanks for reading!
Resi

i am using excel 2003 on Windows XP

 

[Jacob Skaria]

Not sure how your data is arranged..Suppose you have the date and overtime
hours Col A and ColB like below

3/11/2009 1
3/12/2009 2
3/13/2009 3
3/14/2009 2
3/15/2009 1

the below formula will return the number of hours applicable as of
today..Today being 13 th the below formula will total the hours after March
13th 2009 which is 2 months back...and the total hours would be 3 (from 14th
mar and 15th mar)

=SUMIF(A1:A4,">" & DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),B1:B4)

If this post helps click Yes

 

[Jacob Skaria]

Not sure how your data is arranged..Suppose you have the date and overtime
hours Col A and ColB like below

3/11/2009 1
3/12/2009 2
3/13/2009 3
3/14/2009 2
3/15/2009 1

the below formula will return the number of hours applicable as of
today..Today being 13 th the below formula will total the hours after March
13th 2009 which is 2 months back...and the total hours would be 3 (from 14th
mar and 15th mar)

=SUMIF(A1:A4,">" & DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),B1:B4)

If this post helps click Yes

 

[Resi]

Hi Jacob
just a bit confused. where i am putting the formula...
the timesheet work on a monthly worksheet april to march.
the hours worked are 'tracked' on a front sheet, which records the monthly
overtime (cell E9:S9) as well as a running total overtime (E10:S10)

so should i put your solution into the E10:S10 cells?

will have a go again.
thanks for the help
Resi

 

[Resi]

Hi Jacob
just a bit confused. where i am putting the formula...
the timesheet work on a monthly worksheet april to march.
the hours worked are 'tracked' on a front sheet, which records the monthly
overtime (cell E9:S9) as well as a running total overtime (E10:S10)

so should i put your solution into the E10:S10 cells?

will have a go again.
thanks for the help
Resi

 

[Resi]

ok i tried to apply it and i think my problem is that the overtime is worked
on a monthly basis and not daily basis, as staff are contracted XXhrs month
to work (this varies is staff full-time or part-time.

also it is based on TODAY date... but what i am looking for i think is
different:

if i hhave done 2hrs overtime in April, these have to be take before the end
of June, but in May i have taken 1hr, so on 1st July i need to have a formula
which deducts the overtime remaining not take as toil.(i.e. the remaining 1hr)

does this makes more sense?
Hope so!
Resi

 

[Resi]

ok i tried to apply it and i think my problem is that the overtime is worked
on a monthly basis and not daily basis, as staff are contracted XXhrs month
to work (this varies is staff full-time or part-time.

also it is based on TODAY date... but what i am looking for i think is
different:

if i hhave done 2hrs overtime in April, these have to be take before the end
of June, but in May i have taken 1hr, so on 1st July i need to have a formula
which deducts the overtime remaining not take as toil.(i.e. the remaining 1hr)

does this makes more sense?
Hope so!
Resi