Hello Shane, thanks for taking the time to answer my problem.
I have two sheets, Home and A. The home page is just the names going down
and has the results of page A after the 1's and L's have been entered.
The A page has in column A1 going down, the year and month from Jan 2008 to
Dec 2009. 508 days which is the working days available for 2 years minus
weekends and bank holidays. Column B1 has the actual day i.e. 1st, 2nd, 3rd
etc. There is no space between the years as I couldn't get the sumproduct
formula to work with a space between the years. From D1 & D2 going across to
DI1 & DI2 are the sumproduct formula's for everyone. D3 to DI are the names.
Again I had to go across the sheet with names as I ran out of columns trying
to put the dates across.
If I enter in cell D5 a 1 or L the result appears in D1, the result is then
transferred to the Home sheet under that name. Enter anther 1 or L in D19
then its added etc etc. This happens for everyone on the sheet obviously.
All this works great with the help of all the kind people like yourself.
This is the first year and it's been a godsend not having to work out
manually how many times and how many days people have been absent etc. But
as this year draws to a close it throws up the problem for next year. It
would be easy if we could start a new year afresh, but we use a rolling
year. If someone s off in March 19 2008, that stays on their record until we
get to March 19 2009, any points they have gained is added. When we get to
March 20 2009 then the absence on March 19 2008 is then deleted. I could
alter the date in column A to the actual date of month year and day and
delete the days not required i.e. weekends etc if it makes it easier. Being
human, I make mistakes, it could be that I miss deleting an absence which
could potentially cause someone to be put on a disciplinary for too much
time off. So what I am trying to do is for everything to be deleted on a
daily basis automatically or when I open the workbook.
It's a shame we are not given these tools to work with, but we are expected
to keep track ourselves manually. There must be applications out there which
our company could buy, but alas it doesn't happen. Thank you once again.

Bryan.

"ShaneDevenshire" wrote in
message ...
Hi,

I'm going to guess that the emplyee name go across row 1? If so, here is
an
approach that will work automatically, without a macro, and will let you
retain history!

Leave the first sheet as it is, in my the following example I call it A
for
absence (it makes to formula easier to write).

On another sheet I list the employees across the top starting in cell A3
In cell B1 enter the formula =TODAY(), in cell A1 enter =EDATE(B1,-12)
(the EDATE function is an analysis toolpak function so you must attach
it -
Tools, Add-ins, and check the box beside Analysis ToolPak) You may decide
to
use a slightly different formula, I'll discuss that later. These formulas
update automatically whenever the spreadsheet recalculates, including when
it
opens.

In cell A4 enter the formula:
=SUMPRODUCT(--($H$2=Sheet4!$A$2:$A$732),--($G$2<=Sheet4!$A$2:$A$732),--(Sheet4!C$2:C$732=1))
This formula counts the 1's for the current year.

In cell A5 enter the formula:
=SUMPRODUCT(--($H$2=Sheet4!$A$2:$A$732),--($G$2<=Sheet4!$A$2:$A$732),--(Sheet4!C$2:C$732="L"))
This formula counts the "L"'s for the current year.

Copy these formula across under each employee.
Note I assume from your discription that the first employee appears in
column C of the original worksheet, you will need to adjust this if that
is
not the case, (replace the C2:C732 range with the appropriate range for
the
first employee.

My formula uses EDATE which calculates the same date one year earlier, you
may need to adjust that in some way, for example, you might define a year
as
365 days, in which case you could replace EDATE with =B1-365.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Bryan De-Lara" wrote:


Jan-08 2 1
Jan-08 3 2
Jan-08 4 3
Jan-08 7 4
Jan-08 8 5
Jan-08 9 6




Problem is:-

I have a spreadsheet from A1 TO DG600

In the cells I have either a 1 or L

The A column is the dates for 2 years going down from Jan 2008 until 31
Dec
2009, column B is the actual day.

All the dates represent a working day throughout the year minus weekends
and
bank holiday's.

What I need to do it delete on 02 Jan 2009 anything that is in those
cells
on that is in row on 02 Jan 2008, leaving the 03 Jan 2008 until we reach
03
Jan 2009. As each day passes then the corresponding cell contents are
deleted. So if I only open it once every few days it will delete the
previous corresponding date the year before, thus having a rolling year.
I
have tried to use a macro, but my macro writing is not up to scratch, in
fact I would put it as impossible for me. Is there any chance I can use a
simple formula for this.

I don't want to sound if I'm begging but I have been trying for the past
10
months on my own without must joy.