Deleting by date automatically
 

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.

Deleting by date automatically
 

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

Deleting by date automatically
 

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

Deleting by date automatically
 

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.

Deleting by date automatically
 

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.

Deleting by date automatically
 

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

Deleting by date automatically
 

Nice!

Deleting by date automatically
 

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

Deleting by date automatically
 

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

Deleting by date automatically
 

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

Deleting by date automatically
 

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

Deleting by date automatically
 

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

Deleting by date automatically
 

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" wrote in message
...
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

Deleting by date automatically
 

Past midnight!?!

It is only 4 PM here. Where are you?

Tom

Deleting by date automatically
 

UK.

"TomPl" wrote in message
...
Past midnight!?!

It is only 4 PM here. Where are you?

Tom

Deleting by date automatically
 

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(--(D3:D766=1),--(D4:D767<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" wrote in message
...
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

Deleting by date automatically
 

What formula is in cell A!D775

Tom

Deleting by date automatically
 

Tom, I've now added to it A!D775 has moved to A!D2396 and the formula is
=SUM(D6:D2936) 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(--(D4:D2936=1),--(D5:D2937<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" wrote in message
...
What formula is in cell A!D775

Tom

Deleting by date automatically
 

Good evening Bryan,

the count of occurances.
=SUMPRODUCT(--(D4:D2936=1),--(D5:D2937<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(--(D4:D2936=1),--(D5:D2937<1),--($A4:$A2936=TODAY()-365))

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(D4:D2936,--($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

Deleting by date automatically
 

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.

"Bryan De-Lara" wrote in message
...
Am I the first to send a message 23/01/2009?


"Bryan De-Lara" wrote in message
...
That's good Tom, it works across now when I drag the formula.
There is one problem, I've changed my clock to simulate next year. I have
put two 1's together then a single 1 and another single 1. This before
the Today formula would have read so...
s s d total points
3 3 4 36

After with date changed
s s d total points
2 2 4 16 when it should read 2 2 2 = 8 then
first and second s is correct, it still counts the total days. I think
that is because of =SUM(D6:D2936) which is in D2948. I put it that low so
as not to cause the circular ref and it gave me space to add something
there without have to insert cells. Should I move it to the top?

Bryan.

p s what does the $ do in ),--($A4:$A2936=TODAY()-365)), I take it, it
stops the A from changing to B etc.



"TomPl" wrote in message
...
Good evening Bryan,

the count of occurances.
=SUMPRODUCT(--(D4:D2936=1),--(D5:D2937<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(--(D4:D2936=1),--(D5:D2937<1),--($A4:$A2936=TODAY()-365))

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(D4:D2936,--($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

Deleting by date automatically
 

Bryan,

You indicated that the formula in cell D2948 is:
=SUM(D6:D2936)

To exclude stale "1"s the formula should be:
=SUMPRODUCT(--(D5:D2936=1),--($A5:$A2936=TODAY()-365))

The dollar signs prevent the "A" from changing to "B", "C", "D" when you
copy the formula accross the row.

I like these types of formulas at the top of the data but that is up to you.
As long as it works, and I hope it does.

Tom

Deleting by date automatically
 

Tom,

How can I thank you enough. Everything works greaaat thanks. Without the
help of people like yourself, there would be a lot of frustrated people.
I must admit though, I've been dong this project for quite sometime, and I
do try my best with the help pages etc, but there are just some things that
we need help with.
No doubt when I start my next project I will need more help.
Thanks a million times for your time.

Bryan.

P.S. All my questions have been answered and completed.


"TomPl" wrote in message
...
Bryan,

You indicated that the formula in cell D2948 is:
=SUM(D6:D2936)

To exclude stale "1"s the formula should be:
=SUMPRODUCT(--(D5:D2936=1),--($A5:$A2936=TODAY()-365))

The dollar signs prevent the "A" from changing to "B", "C", "D" when you
copy the formula accross the row.

I like these types of formulas at the top of the data but that is up to
you.
As long as it works, and I hope it does.

Tom

Deleting by date automatically
 

Tom, I have been playing today with my nice new workbook. One thing I forgot
to do is to try and put holidays on the sheet, sometimes people take half
days so I tried to modify the formula from =SUMPRODUCT(--(D4:D2935="H")) to
=SUMPRODUCT(--(D4:D2935="H"))--SUMPRODUCT(--(D4:D2935="HD"=.5)) but it
registers 2 instead of 1.5. Can you tell me where I am going wrong please.
Bryan.

"Bryan De-Lara" wrote in message
...
Tom,

How can I thank you enough. Everything works greaaat thanks. Without the
help of people like yourself, there would be a lot of frustrated people.
I must admit though, I've been dong this project for quite sometime, and I
do try my best with the help pages etc, but there are just some things
that we need help with.
No doubt when I start my next project I will need more help.
Thanks a million times for your time.

Bryan.

P.S. All my questions have been answered and completed.


"TomPl" wrote in message
...
Bryan,

You indicated that the formula in cell D2948 is:
=SUM(D6:D2936)

To exclude stale "1"s the formula should be:
=SUMPRODUCT(--(D5:D2936=1),--($A5:$A2936=TODAY()-365))

The dollar signs prevent the "A" from changing to "B", "C", "D" when you
copy the formula accross the row.

I like these types of formulas at the top of the data but that is up to
you.
As long as it works, and I hope it does.

Tom

Deleting by date automatically
 

Look in Excel help for the syntax of the functions you are using, and look
at the syntax of the formula sugested earlier. Think about what the formula
is doing, and compare that with what you want it to do.

If you are struggling to debug your formula, break it up into manageable
chunks and see what each part is doing.
You could put =SUMPRODUCT(--(D4:D2935="H")) in H1,
=SUMPRODUCT(--(D4:D2935="HD"=0.5)) in H2, and =H1--H2 in H3.
If it still isn't making sense to you, look at syntax of each of those in
turn.
--
David Biddulph

"Bryan De-Lara" wrote in message
...
Tom, I have been playing today with my nice new workbook. One thing I
forgot to do is to try and put holidays on the sheet, sometimes people
take half days so I tried to modify the formula from
=SUMPRODUCT(--(D4:D2935="H")) to
=SUMPRODUCT(--(D4:D2935="H"))--SUMPRODUCT(--(D4:D2935="HD"=.5)) but it
registers 2 instead of 1.5. Can you tell me where I am going wrong please.
Bryan.

"Bryan De-Lara" wrote in message
...
Tom,

How can I thank you enough. Everything works greaaat thanks. Without the
help of people like yourself, there would be a lot of frustrated people.
I must admit though, I've been dong this project for quite sometime, and
I do try my best with the help pages etc, but there are just some things
that we need help with.
No doubt when I start my next project I will need more help.
Thanks a million times for your time.

Bryan.

P.S. All my questions have been answered and completed.


"TomPl" wrote in message
...
Bryan,

You indicated that the formula in cell D2948 is:
=SUM(D6:D2936)

To exclude stale "1"s the formula should be:
=SUMPRODUCT(--(D5:D2936=1),--($A5:$A2936=TODAY()-365))

The dollar signs prevent the "A" from changing to "B", "C", "D" when you
copy the formula accross the row.

I like these types of formulas at the top of the data but that is up to
you.
As long as it works, and I hope it does.

Tom

Deleting by date automatically
 

Thanks David, I did play with it again yesterday and found this works,
=SUMPRODUCT(--(D4:D2035="d"))--(--SUMPRODUCT(--(D4:D2035="HD"))*0.5)
Not exactly the same as yours, but at least I managed it...thanks.

Bryan.

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Look in Excel help for the syntax of the functions you are using, and look
at the syntax of the formula sugested earlier. Think about what the
formula is doing, and compare that with what you want it to do.

If you are struggling to debug your formula, break it up into manageable
chunks and see what each part is doing.
You could put =SUMPRODUCT(--(D4:D2935="H")) in H1,
=SUMPRODUCT(--(D4:D2935="HD"=0.5)) in H2, and =H1--H2 in H3.
If it still isn't making sense to you, look at syntax of each of those in
turn.
--
David Biddulph

"Bryan De-Lara" wrote in message
...
Tom, I have been playing today with my nice new workbook. One thing I
forgot to do is to try and put holidays on the sheet, sometimes people
take half days so I tried to modify the formula from
=SUMPRODUCT(--(D4:D2935="H")) to
=SUMPRODUCT(--(D4:D2935="H"))--SUMPRODUCT(--(D4:D2935="HD"=.5)) but it
registers 2 instead of 1.5. Can you tell me where I am going wrong
please.
Bryan.

"Bryan De-Lara" wrote in message
...
Tom,

How can I thank you enough. Everything works greaaat thanks. Without the
help of people like yourself, there would be a lot of frustrated people.
I must admit though, I've been dong this project for quite sometime, and
I do try my best with the help pages etc, but there are just some things
that we need help with.
No doubt when I start my next project I will need more help.
Thanks a million times for your time.

Bryan.

P.S. All my questions have been answered and completed.


"TomPl" wrote in message
...
Bryan,

You indicated that the formula in cell D2948 is:
=SUM(D6:D2936)

To exclude stale "1"s the formula should be:
=SUMPRODUCT(--(D5:D2936=1),--($A5:$A2936=TODAY()-365))

The dollar signs prevent the "A" from changing to "B", "C", "D" when
you
copy the formula accross the row.

I like these types of formulas at the top of the data but that is up to
you.
As long as it works, and I hope it does.

Tom

Deleting by date automatically
 

Perhaps you might want to think about what you're achieving with each of the
double unary minuses?

Why have you used
=SUMPRODUCT(--(D4:D2035="d"))--(--SUMPRODUCT(--(D4:D2035="HD"))*0.5)
instead of =SUMPRODUCT(--(D4:D2035="d"))+SUMPRODUCT(--(D4:D2035="HD"))*0.5
?
--
David Biddulph

"Bryan De-Lara" wrote in message
...
Thanks David, I did play with it again yesterday and found this works,
=SUMPRODUCT(--(D4:D2035="d"))--(--SUMPRODUCT(--(D4:D2035="HD"))*0.5)
Not exactly the same as yours, but at least I managed it...thanks.

Bryan.

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Look in Excel help for the syntax of the functions you are using, and
look at the syntax of the formula sugested earlier. Think about what the
formula is doing, and compare that with what you want it to do.

If you are struggling to debug your formula, break it up into manageable
chunks and see what each part is doing.
You could put =SUMPRODUCT(--(D4:D2935="H")) in H1,
=SUMPRODUCT(--(D4:D2935="HD"=0.5)) in H2, and =H1--H2 in H3.
If it still isn't making sense to you, look at syntax of each of those in
turn.
--
David Biddulph

"Bryan De-Lara" wrote in message
...
Tom, I have been playing today with my nice new workbook. One thing I
forgot to do is to try and put holidays on the sheet, sometimes people
take half days so I tried to modify the formula from
=SUMPRODUCT(--(D4:D2935="H")) to
=SUMPRODUCT(--(D4:D2935="H"))--SUMPRODUCT(--(D4:D2935="HD"=.5)) but it
registers 2 instead of 1.5. Can you tell me where I am going wrong
please.
Bryan.

"Bryan De-Lara" wrote in message
...
Tom,

How can I thank you enough. Everything works greaaat thanks. Without
the help of people like yourself, there would be a lot of frustrated
people.
I must admit though, I've been dong this project for quite sometime,
and I do try my best with the help pages etc, but there are just some
things that we need help with.
No doubt when I start my next project I will need more help.
Thanks a million times for your time.

Bryan.

P.S. All my questions have been answered and completed.


"TomPl" wrote in message
...
Bryan,

You indicated that the formula in cell D2948 is:
=SUM(D6:D2936)

To exclude stale "1"s the formula should be:
=SUMPRODUCT(--(D5:D2936=1),--($A5:$A2936=TODAY()-365))

The dollar signs prevent the "A" from changing to "B", "C", "D" when
you
copy the formula accross the row.

I like these types of formulas at the top of the data but that is up
to you.
As long as it works, and I hope it does.

Tom

Deleting by date automatically
 

Well, as I am not that good, or should I say that well up on Excel I didn't
know that you could use + between, another snippet filed for future use,
Thank you.

Bryan.

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Perhaps you might want to think about what you're achieving with each of
the double unary minuses?

Why have you used
=SUMPRODUCT(--(D4:D2035="d"))--(--SUMPRODUCT(--(D4:D2035="HD"))*0.5)
instead of =SUMPRODUCT(--(D4:D2035="d"))+SUMPRODUCT(--(D4:D2035="HD"))*0.5
?
--
David Biddulph

"Bryan De-Lara" wrote in message
...
Thanks David, I did play with it again yesterday and found this works,
=SUMPRODUCT(--(D4:D2035="d"))--(--SUMPRODUCT(--(D4:D2035="HD"))*0.5)
Not exactly the same as yours, but at least I managed it...thanks.

Bryan.

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Look in Excel help for the syntax of the functions you are using, and
look at the syntax of the formula sugested earlier. Think about what
the formula is doing, and compare that with what you want it to do.

If you are struggling to debug your formula, break it up into manageable
chunks and see what each part is doing.
You could put =SUMPRODUCT(--(D4:D2935="H")) in H1,
=SUMPRODUCT(--(D4:D2935="HD"=0.5)) in H2, and =H1--H2 in H3.
If it still isn't making sense to you, look at syntax of each of those
in turn.
--
David Biddulph

"Bryan De-Lara" wrote in message
...
Tom, I have been playing today with my nice new workbook. One thing I
forgot to do is to try and put holidays on the sheet, sometimes people
take half days so I tried to modify the formula from
=SUMPRODUCT(--(D4:D2935="H")) to
=SUMPRODUCT(--(D4:D2935="H"))--SUMPRODUCT(--(D4:D2935="HD"=.5)) but it
registers 2 instead of 1.5. Can you tell me where I am going wrong
please.
Bryan.

"Bryan De-Lara" wrote in message
...
Tom,

How can I thank you enough. Everything works greaaat thanks. Without
the help of people like yourself, there would be a lot of frustrated
people.
I must admit though, I've been dong this project for quite sometime,
and I do try my best with the help pages etc, but there are just some
things that we need help with.
No doubt when I start my next project I will need more help.
Thanks a million times for your time.

Bryan.

P.S. All my questions have been answered and completed.


"TomPl" wrote in message
...
Bryan,

You indicated that the formula in cell D2948 is:
=SUM(D6:D2936)

To exclude stale "1"s the formula should be:
=SUMPRODUCT(--(D5:D2936=1),--($A5:$A2936=TODAY()-365))

The dollar signs prevent the "A" from changing to "B", "C", "D" when
you
copy the formula accross the row.

I like these types of formulas at the top of the data but that is up
to you.
As long as it works, and I hope it does.

Tom

Deleting by date automatically
 

Yes, if you want to add two variables, the symbol to use is +.
--
David Biddulph

"Bryan De-Lara" wrote in message
...
Well, as I am not that good, or should I say that well up on Excel I
didn't know that you could use + between, another snippet filed for future
use, Thank you.

Bryan.

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Perhaps you might want to think about what you're achieving with each of
the double unary minuses?

Why have you used
=SUMPRODUCT(--(D4:D2035="d"))--(--SUMPRODUCT(--(D4:D2035="HD"))*0.5)
instead of
=SUMPRODUCT(--(D4:D2035="d"))+SUMPRODUCT(--(D4:D2035="HD"))*0.5 ?
--
David Biddulph

"Bryan De-Lara" wrote in message
...
Thanks David, I did play with it again yesterday and found this works,
=SUMPRODUCT(--(D4:D2035="d"))--(--SUMPRODUCT(--(D4:D2035="HD"))*0.5)
Not exactly the same as yours, but at least I managed it...thanks.

Bryan.

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Look in Excel help for the syntax of the functions you are using, and
look at the syntax of the formula sugested earlier. Think about what
the formula is doing, and compare that with what you want it to do.

If you are struggling to debug your formula, break it up into
manageable chunks and see what each part is doing.
You could put =SUMPRODUCT(--(D4:D2935="H")) in H1,
=SUMPRODUCT(--(D4:D2935="HD"=0.5)) in H2, and =H1--H2 in H3.
If it still isn't making sense to you, look at syntax of each of those
in turn.
--
David Biddulph

"Bryan De-Lara" wrote in message
...
Tom, I have been playing today with my nice new workbook. One thing I
forgot to do is to try and put holidays on the sheet, sometimes people
take half days so I tried to modify the formula from
=SUMPRODUCT(--(D4:D2935="H")) to
=SUMPRODUCT(--(D4:D2935="H"))--SUMPRODUCT(--(D4:D2935="HD"=.5)) but it
registers 2 instead of 1.5. Can you tell me where I am going wrong
please.
Bryan.

"Bryan De-Lara" wrote in message
...
Tom,

How can I thank you enough. Everything works greaaat thanks. Without
the help of people like yourself, there would be a lot of frustrated
people.
I must admit though, I've been dong this project for quite sometime,
and I do try my best with the help pages etc, but there are just some
things that we need help with.
No doubt when I start my next project I will need more help.
Thanks a million times for your time.

Bryan.

P.S. All my questions have been answered and completed.


"TomPl" wrote in message
...
Bryan,

You indicated that the formula in cell D2948 is:
=SUM(D6:D2936)

To exclude stale "1"s the formula should be:
=SUMPRODUCT(--(D5:D2936=1),--($A5:$A2936=TODAY()-365))

The dollar signs prevent the "A" from changing to "B", "C", "D" when
you
copy the formula accross the row.

I like these types of formulas at the top of the data but that is up
to you.
As long as it works, and I hope it does.

Tom