Rolling year

[Guest]

I have an attendance report and our policy is based on 20 days to be taken in
a year on a rolling basis.
Example: During the 2005 year employee took the following sick days
June 4 2005, August 19, 2005, October 23 2005

So, for the year 2006 this employee starts with 17 sick days left and on
June 4 one day is added, on August 19 another day is added, etc.
AND when this employees takes a sick day during the year of 2006 it has to
be deducted

Please tell me there is a formula that will calculate the correct total

 

[Mallycat]

Please tell me there is a formula that will calculate the correct total

I can't think of an easy way. The COUNTIF function would be the
logical one to use but the criteria options are too limited. You could
do it in 2 steps though. Assuming you have a row or column of dates
where someone is absent, then create a formula that checks each cell to
see if it is in the last 12 months ie (assume C1 has the date of
absence)
=IF(TODAY()-C1<365,1,0)

copy the forumla for all absent dates, then sum the cells to see how
many absent they have had.

Matt

 

[Guest]

Mallycat
YES! awesome
I used three columns one for 2005, one for 2006 and the last one for your
formula. Then hid the 2005 and the formula columns just leaving the 2006
column to update automatically. Thank you soooo much