Deleting by date automatically

[TomPl]

Bryan,

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

To exclude stale "1"s the formula should be:
=SUMPRODUCT(--(D52936=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

 

[Bryan De-Lara]

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.

 

[Bryan De-Lara]

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(--(D42935="H")) to
=SUMPRODUCT(--(D42935="H"))--SUMPRODUCT(--(D42935="HD"=.5)) but it
registers 2 instead of 1.5. Can you tell me where I am going wrong please.
Bryan.

 

[David Biddulph]

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(--(D42935="H")) in H1,
=SUMPRODUCT(--(D42935="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.

 

[Bryan De-Lara]

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

Bryan.

 

[David Biddulph]

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

Why have you used
=SUMPRODUCT(--(D42035="d"))--(--SUMPRODUCT(--(D42035="HD"))*0.5)
instead of =SUMPRODUCT(--(D42035="d"))+SUMPRODUCT(--(D42035="HD"))*0.5
?

 

[Bryan De-Lara]

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]

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

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.

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

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

Thanks David, I did play with it again yesterday and found this works,
=SUMPRODUCT(--(D42035="d"))--(--SUMPRODUCT(--(D42035="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(--(D42935="H")) in H1,
=SUMPRODUCT(--(D42935="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

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(--(D42935="H")) to
=SUMPRODUCT(--(D42935="H"))--SUMPRODUCT(--(D42935="HD"=.5)) but it
registers 2 instead of 1.5. Can you tell me where I am going wrong
please.
Bryan.

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.


Bryan,

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

To exclude stale "1"s the formula should be:
=SUMPRODUCT(--(D52936=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

 

[Bryan De-Lara]

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(D62936) 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.

 

[Bryan De-Lara]

Am I the first to send a message 23/01/2009?

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(D62936) 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.