Deleting by date automatically

[Bryan De-Lara]

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.

 

[TomPl]

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

 

[Bryan De-Lara]

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(--(D3766=1),--(D4767<>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).

 

[ShaneDevenshire]

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.

 

[Bryan De-Lara]

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.

 

[TomPl]

Bryan,

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

If you change your formula =SUMPRODUCT(--(D3766=1),--(D4767<>1)) to
=SUMPRODUCT(--(D3766=1),--(D4767<>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

 

[TomPl]

Nice!

 

[Bryan De-Lara]

Tom,

=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]

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 De-Lara]

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

 

[Bryan De-Lara]

Tom number is 39446.00

 

[TomPl]

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

 

[Bryan De-Lara]

Thanks Tom, it's past midnight now so will do that tomorrow. I'll hopefully
get back to you saying job done and working fine.... thanks again.

Bryan.

 

[TomPl]

Past midnight!?!

It is only 4 PM here. Where are you?

Tom

 

[Bryan De-Lara]

UK.

 

[Bryan De-Lara]

Tom,
Good news, it works, but there is one slight problem which I don't know if
another fix can be done. I've got the 2 pages set up something like this.
Home page

Name S S D Total Points

Col A Col B Col C Col D Col E

J Bloggs 1 1 2 8



Formula for Col B is =A!D2

Formula for Col C is =B3

Formula for Col D is =A!D775

Formula for Col E is =B3*C3*D3



Formula for Col B (which is where the 1's are input),from the A page which
is in D2 is
=SUMPRODUCT(--(D3766=1),--(D4767<>1),--(A3:A766>=TODAY()-365)).



This does as you say ignore anything from 365 days before. But, Col A & B
reset but D still counts the days. That needs to go down as well.



If a body takes 3rd March of, single day, then 1 in August it would be:-



Col A Col B Col C Col D Col E

J Bloggs 2 2 2 8



When you get past 3rd March it resets to

Col A Col B Col C Col D Col E

J Bloggs 1 1 2 2
which is incorrect, it would need to read



Col A Col B Col C Col D Col E

J Bloggs 1 1 1 1



I hope I have explained it a little better.

Other wise this is good progress for me, thanks to everyone.

 

[TomPl]

What formula is in cell A!D775

Tom

 

[Bryan De-Lara]

Tom, I've now added to it A!D775 has moved to A!D2396 and the formula is
=SUM(D62936) this adds the days if it's 1 on it's own or multiple 1's
together.
=SUMPRODUCT(--(G4:G2936=1),--(G5:G2937<>1)) is to count the times 1 appears
in batched. I.E. a single 1 or if three 1's together with be two instances.
That's how I get s*s*d. s=occurrence * s * days. So (s)
SUMPRODUCT(--(G4:G2936=1),--(G5:G2937<>1)) = ( s)
SUMPRODUCT(--(G4:G2936=1),--(G5:G2937<>1)) * (d) A!D2396

Strange but the first two cells where I have
=SUMPRODUCT(--(D42936=1),--(D52937<>1),--(A4:A2936>=TODAY()-365)) works
fine but the 3rd cell onwards doesn't. I dragged them across. This is the
3rd cell
=SUMPRODUCT(--(F4:F2936=1),--(F5:F2937<>1),--(C4:C2936>=TODAY()-365)) I
cannot see any difference.

 

[TomPl]

Good evening Bryan,

Re: the count of occurances.
=SUMPRODUCT(--(D42936=1),--(D52937<>1),--(A4:A2936>=TODAY()-365))
=SUMPRODUCT(--(F4:F2936=1),--(F5:F2937<>1),--(C4:C2936>=TODAY()-365))
The date column must refer to column A. Try this.
=SUMPRODUCT(--(D42936=1),--(D52937<>1),--($A4:$A2936>=TODAY()-365))

Re: the count of days.
Based on the above formula, I will assume that your data goes between
rows 4 and 2936. Row 2937 should remain blank.
If your formula is in row 2936 (as you indicated) it has a circular reference.
That is not good.
Your formula should be:
=SUMPRODUCT(D42936,--($A4:$A2936>=TODAY()-365))
And I assume that it is located in row 2938 to avoid the circular reference.
That should give you the total number of "1"s during the last 365 days.

Let me know if this works.

Tom

 

[Bryan De-Lara]

No Tom it didn't As you can see by the date 23/01/2009 I tried it with my
clock changed. There was no reply so I thought you'd given up on me, which I
couldn't blame you, I would have.

Bryan.