Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto zero cell data on a function???
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto zero cell data on a function???
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 "Resi" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto zero cell data on a function???
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 "Jacob Skaria" wrote: 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 "Resi" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto zero cell data on a function???
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 "Jacob Skaria" wrote: 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 "Resi" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count function in Auto-Filtered data | Excel Discussion (Misc queries) | |||
How do i remove the auto date function from a cell to allow # rang | Excel Discussion (Misc queries) | |||
Excel 2002: How to overcome inconsistancy in auto cell function ? | Excel Discussion (Misc queries) | |||
Excel 2002 : Can auto cell entry function more than once ? | Excel Discussion (Misc queries) | |||
How to auto count data in an empty cell to be 0. Blank cell=0 | Excel Discussion (Misc queries) |