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.

Your question is not perfectly clear.

Why do you want to delete? Does that data affect some results or formulas?
It might be easier to change those formulas.

Do you want to delete the row, or clear the contents of the whole row, or
clear the contents of the row leaving the date and day in columns A and B?

Do you want this to happen automatically when you open the workbook or do
you want to control the event manually?

A few more details please.

Tom

Tom, my workbook contains either a 1 or a L. 1 is for days absence the L is
for a lateness. You get points for every day absent. That stays on the books
so to speak for 1 year.
Say the first day was Feb 28 2008 then on 28 Feb 2009 that would be deleted.
As you go through the year then each absence gains more points. But when you
pass a date in the year before the points reduce. Because we have over 120
employees it would be safer to get it deleted automatically rather than miss
one manually deleting. We have a system called Bradfords which records like
this s s d. 1 day off would be 1*1*1. the second time that year for 2 days
in a row would 2 (2nd occasion)*2 *3 (days off that year = 12 points and so
on. The next single day would be 3*3*3=27. So reducing after a year if no
more time off. I want to be able to delete automatically when ever I open
the workbook. If no one takes any time off for 4 days there would be no need
to open it, but on the fifth day if someone is absent, then as soon as it's
opened it would delete exactly one year before on that date. Without sending
my workbook, I'm finding it hard to explain. I tried pasting some of my
workbook here but it didn't show.
The formula I use to calculate is =SUMPRODUCT(--(D3:D766=1),--(D4:D767<1)).
Each day or more is considered an occasion whether it be 1 or more days. The
calculation after that is simple 1*1*1=3(1 day off in total. 2*2*2=8 (2
days off in total) 3*3*8=72 (8 days off in total on the 3rd occasion).



"TomPl" wrote in message
...
Your question is not perfectly clear.

Why do you want to delete? Does that data affect some results or
formulas?
It might be easier to change those formulas.

Do you want to delete the row, or clear the contents of the whole row, or
clear the contents of the row leaving the date and day in columns A and B?

Do you want this to happen automatically when you open the workbook or do
you want to control the event manually?

A few more details please.

Tom

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.

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.

Nice!

Bryan,

Shane's approach makes good sense. The only change I would make is to avoid
the addin.

If you change your formula =SUMPRODUCT(--(D3:D766=1),--(D4:D767<1)) to
=SUMPRODUCT(--(D3:D766=1),--(D4:D767<1),--(A3:A766 =Today()-365)) it will
ignore absenses more than 365 days prior to the current date. It is not
necessary to delete them, just ignore them.

Yeah, yeah, leap year creates a concern. So when leap year is a factor
change 365 to 366.

Tom

Tom,
The formula =SUMPRODUCT(--(D3:D766=1),--(D4:D767<1),--(A3:A766
=Today()-365)), I work on a 254 day year, will this formula cause a problem
as it's 365? I have the dates going down in the column as 1 2 3 4 5 8 9
10 11 12 15 16 17 18 19 1 being a Monday etc with no spaces obviously
weekends are ignored. If I use the formula as above, will I need to add
week-ends and public holidays but not have anything in those cells?

Bryan.

"TomPl" wrote in message
...
Bryan,

Shane's approach makes good sense. The only change I would make is to
avoid
the addin.

If you change your formula =SUMPRODUCT(--(D3:D766=1),--(D4:D767<1)) to
=SUMPRODUCT(--(D3:D766=1),--(D4:D767<1),--(A3:A766 =Today()-365)) it
will
ignore absenses more than 365 days prior to the current date. It is not
necessary to delete them, just ignore them.

Yeah, yeah, leap year creates a concern. So when leap year is a factor
change 365 to 366.

Tom

Bryan,

I assume that you are useing date values in column A. Excel understands
10/25/08 to be the value 39746 formated to date.
If you are adding some other value in column A, then I am confused.

Change the format of cell A3 to number and tell me what the result is.

Tom

Tom Col A is month and year date col B is day I.E. 1st 2nd 3rd

"TomPl" wrote in message
...
Bryan,

I assume that you are useing date values in column A. Excel understands
10/25/08 to be the value 39746 formated to date.
If you are adding some other value in column A, then I am confused.

Change the format of cell A3 to number and tell me what the result is.

Tom

Tom number is 39446.00
"TomPl" wrote in message
...
Bryan,

I assume that you are useing date values in column A. Excel understands
10/25/08 to be the value 39746 formated to date.
If you are adding some other value in column A, then I am confused.

Change the format of cell A3 to number and tell me what the result is.

Tom

Bryan,

If I was designing this worksheet I would have the actual date in column A.
In your case cell A3 has the date December 30, 2007. December 31, 2007 is
Monday and is not a holiday so cell A4 should be December 31, 2007.

If you make column A actual dates rather than year/month everything will
work fine, so make it so!

Tom