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

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

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

Past midnight!?!

It is only 4 PM here. Where are you?

Tom

UK.

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

It is only 4 PM here. Where are you?

Tom

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),--(D47671),--(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

What formula is in cell A!D775

Tom

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:G29371)) 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:G29371)) = ( s)
SUMPRODUCT(--(G4:G2936=1),--(G5:G29371)) * (d) A!D2396

Strange but the first two cells where I have
=SUMPRODUCT(--(D42936=1),--(D529371),--(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:F29371),--(C4:C2936=TODAY()-365)) I
cannot see any difference.




"TomPl" wrote in message
...
What formula is in cell A!D775

Tom

Good evening Bryan,

the count of occurances.
=SUMPRODUCT(--(D42936=1),--(D529371),--(A4:A2936=TODAY()-365))
=SUMPRODUCT(--(F4:F2936=1),--(F5:F29371),--(C4:C2936=TODAY()-365))
The date column must refer to column A. Try this.
=SUMPRODUCT(--(D42936=1),--(D529371),--($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(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

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



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

the count of occurances.
=SUMPRODUCT(--(D42936=1),--(D529371),--(A4:A2936=TODAY()-365))
=SUMPRODUCT(--(F4:F2936=1),--(F5:F29371),--(C4:C2936=TODAY()-365))
The date column must refer to column A. Try this.
=SUMPRODUCT(--(D42936=1),--(D529371),--($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(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