create a forumla based on date
 

I need to create a formula based on date. I need to creat a calcualte to
include other cells only if they meet certain date requirements. The cells
will meet the requirements a certain period of time then will need to be
removed from the formula. I am trying t automate the worksheet so I don't
have to update it once a month to exclude the previous 4 weeks. How can I do
this?

create a forumla based on date
 

Can you provide an example?

Sounds like you will somehow need to use either the NOW() or TODAY() formula.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Kesbutler" wrote:

I need to create a formula based on date. I need to creat a calcualte to
include other cells only if they meet certain date requirements. The cells
will meet the requirements a certain period of time then will need to be
removed from the formula. I am trying t automate the worksheet so I don't
have to update it once a month to exclude the previous 4 weeks. How can I do
this?

create a forumla based on date
 

Here is just an example:

10/10/08 27
9/4/08 36
11/16/08 50
9/28/08 67
11/20/08 56
10/25/08 46
11/1/08 63
10/12/08 23
11/22/08 62
12/2/08 36
11/27/08 59
10/15/08 77
9/15/08 63
11/15/08 21
10/16/08 78
10/9/08 45
12/5/08 34
11/7/08 26
11/11/08 64
9/2/08 74
9/19/08 57
9/4/08 36
10/8/08 25
10/14/08 55
11/8/08 24
10/5/08 32
10/6/08 74
10/7/08 65
10/15/08 52
12/3/08 22

Notice the data is not even sorted. We want the sum of the November data
only.

=SUMPRODUCT((MONTH(A1:A30)=11)*(B1:B30))

To get October data use 10 in place of 11
--
Gary''s Student - gsnu200820


"Kesbutler" wrote:

I need to create a formula based on date. I need to creat a calcualte to
include other cells only if they meet certain date requirements. The cells
will meet the requirements a certain period of time then will need to be
removed from the formula. I am trying t automate the worksheet so I don't
have to update it once a month to exclude the previous 4 weeks. How can I do
this?

create a forumla based on date
 

Here is more specifics:
I am working with financial data by period and the periods fall across
multiple months. From the example below I will need to sum the data from
another cell with the data in this row but as periods are completed I will
need to remove these cells. For example my first calculation will include all
13 periods plus the one additional cell. But in 4 weeks I will no longer need
A1.

A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1
$1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1


"Gary''s Student" wrote:

Here is just an example:

10/10/08 27
9/4/08 36
11/16/08 50
9/28/08 67
11/20/08 56
10/25/08 46
11/1/08 63
10/12/08 23
11/22/08 62
12/2/08 36
11/27/08 59
10/15/08 77
9/15/08 63
11/15/08 21
10/16/08 78
10/9/08 45
12/5/08 34
11/7/08 26
11/11/08 64
9/2/08 74
9/19/08 57
9/4/08 36
10/8/08 25
10/14/08 55
11/8/08 24
10/5/08 32
10/6/08 74
10/7/08 65
10/15/08 52
12/3/08 22

Notice the data is not even sorted. We want the sum of the November data
only.

=SUMPRODUCT((MONTH(A1:A30)=11)*(B1:B30))

To get October data use 10 in place of 11
--
Gary''s Student - gsnu200820


"Kesbutler" wrote:

I need to create a formula based on date. I need to creat a calcualte to
include other cells only if they meet certain date requirements. The cells
will meet the requirements a certain period of time then will need to be
removed from the formula. I am trying t automate the worksheet so I don't
have to update it once a month to exclude the previous 4 weeks. How can I do
this?

create a forumla based on date
 

If I understand you correctly, try this formula in O1.

=SUMPRODUCT(--($A$1:$M$1)*$A$1:$M$1)

Simply delete the value in A1 if you don't need it in 4 weeks time,
and the total will change to a new sum.

HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,









"Kesbutler" wrote:

Here is more specifics:
I am working with financial data by period and the periods fall across
multiple months. From the example below I will need to sum the data from
another cell with the data in this row but as periods are completed I will
need to remove these cells. For example my first calculation will include all
13 periods plus the one additional cell. But in 4 weeks I will no longer need
A1.

A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1
$1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1


"Gary''s Student" wrote:

Here is just an example:

10/10/08 27
9/4/08 36
11/16/08 50
9/28/08 67
11/20/08 56
10/25/08 46
11/1/08 63
10/12/08 23
11/22/08 62
12/2/08 36
11/27/08 59
10/15/08 77
9/15/08 63
11/15/08 21
10/16/08 78
10/9/08 45
12/5/08 34
11/7/08 26
11/11/08 64
9/2/08 74
9/19/08 57
9/4/08 36
10/8/08 25
10/14/08 55
11/8/08 24
10/5/08 32
10/6/08 74
10/7/08 65
10/15/08 52
12/3/08 22

Notice the data is not even sorted. We want the sum of the November data
only.

=SUMPRODUCT((MONTH(A1:A30)=11)*(B1:B30))

To get October data use 10 in place of 11
--
Gary''s Student - gsnu200820


"Kesbutler" wrote:

I need to create a formula based on date. I need to creat a calcualte to
include other cells only if they meet certain date requirements. The cells
will meet the requirements a certain period of time then will need to be
removed from the formula. I am trying t automate the worksheet so I don't
have to update it once a month to exclude the previous 4 weeks. How can I do
this?

create a forumla based on date
 

How can I do it without deleting the value? Is there a way to have it
automatically just drop the cells by looking at the current date and
comparing it to the end date in another cell?

"xlm" wrote:

If I understand you correctly, try this formula in O1.

=SUMPRODUCT(--($A$1:$M$1)*$A$1:$M$1)

Simply delete the value in A1 if you don't need it in 4 weeks time,
and the total will change to a new sum.

HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,









"Kesbutler" wrote:

Here is more specifics:
I am working with financial data by period and the periods fall across
multiple months. From the example below I will need to sum the data from
another cell with the data in this row but as periods are completed I will
need to remove these cells. For example my first calculation will include all
13 periods plus the one additional cell. But in 4 weeks I will no longer need
A1.

A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1
$1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1


"Gary''s Student" wrote:

Here is just an example:

10/10/08 27
9/4/08 36
11/16/08 50
9/28/08 67
11/20/08 56
10/25/08 46
11/1/08 63
10/12/08 23
11/22/08 62
12/2/08 36
11/27/08 59
10/15/08 77
9/15/08 63
11/15/08 21
10/16/08 78
10/9/08 45
12/5/08 34
11/7/08 26
11/11/08 64
9/2/08 74
9/19/08 57
9/4/08 36
10/8/08 25
10/14/08 55
11/8/08 24
10/5/08 32
10/6/08 74
10/7/08 65
10/15/08 52
12/3/08 22

Notice the data is not even sorted. We want the sum of the November data
only.

=SUMPRODUCT((MONTH(A1:A30)=11)*(B1:B30))

To get October data use 10 in place of 11
--
Gary''s Student - gsnu200820


"Kesbutler" wrote:

I need to create a formula based on date. I need to creat a calcualte to
include other cells only if they meet certain date requirements. The cells
will meet the requirements a certain period of time then will need to be
removed from the formula. I am trying t automate the worksheet so I don't
have to update it once a month to exclude the previous 4 weeks. How can I do
this?

create a forumla based on date
 

would you post how your table actually look like
and your expected result. You may need to elaborate more

I will try to help.


--
If this posting was helpful, please click on the Yes button

Thank You

cheers,









"Kesbutler" wrote:

How can I do it without deleting the value? Is there a way to have it
automatically just drop the cells by looking at the current date and
comparing it to the end date in another cell?

"xlm" wrote:

If I understand you correctly, try this formula in O1.

=SUMPRODUCT(--($A$1:$M$1)*$A$1:$M$1)

Simply delete the value in A1 if you don't need it in 4 weeks time,
and the total will change to a new sum.

HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,









"Kesbutler" wrote:

Here is more specifics:
I am working with financial data by period and the periods fall across
multiple months. From the example below I will need to sum the data from
another cell with the data in this row but as periods are completed I will
need to remove these cells. For example my first calculation will include all
13 periods plus the one additional cell. But in 4 weeks I will no longer need
A1.

A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1
$1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1


"Gary''s Student" wrote:

Here is just an example:

10/10/08 27
9/4/08 36
11/16/08 50
9/28/08 67
11/20/08 56
10/25/08 46
11/1/08 63
10/12/08 23
11/22/08 62
12/2/08 36
11/27/08 59
10/15/08 77
9/15/08 63
11/15/08 21
10/16/08 78
10/9/08 45
12/5/08 34
11/7/08 26
11/11/08 64
9/2/08 74
9/19/08 57
9/4/08 36
10/8/08 25
10/14/08 55
11/8/08 24
10/5/08 32
10/6/08 74
10/7/08 65
10/15/08 52
12/3/08 22

Notice the data is not even sorted. We want the sum of the November data
only.

=SUMPRODUCT((MONTH(A1:A30)=11)*(B1:B30))

To get October data use 10 in place of 11
--
Gary''s Student - gsnu200820


"Kesbutler" wrote:

I need to create a formula based on date. I need to creat a calcualte to
include other cells only if they meet certain date requirements. The cells
will meet the requirements a certain period of time then will need to be
removed from the formula. I am trying t automate the worksheet so I don't
have to update it once a month to exclude the previous 4 weeks. How can I do
this?

create a forumla based on date
 

Could you please explain what the double unary minus is doing in this case?
You seem to be doing a calculation of the sum of the squares of the values.
--
David Biddulph

"xlm" wrote in message
...
If I understand you correctly, try this formula in O1.

=SUMPRODUCT(--($A$1:$M$1)*$A$1:$M$1)

Simply delete the value in A1 if you don't need it in 4 weeks time,
and the total will change to a new sum.

HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,

"Kesbutler" wrote:

Here is more specifics:
I am working with financial data by period and the periods fall across
multiple months. From the example below I will need to sum the data from
another cell with the data in this row but as periods are completed I
will
need to remove these cells. For example my first calculation will include
all
13 periods plus the one additional cell. But in 4 weeks I will no longer
need
A1.

A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1
$1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1 $1


"Gary''s Student" wrote:

Here is just an example:

10/10/08 27
9/4/08 36
11/16/08 50
9/28/08 67
11/20/08 56
10/25/08 46
11/1/08 63
10/12/08 23
11/22/08 62
12/2/08 36
11/27/08 59
10/15/08 77
9/15/08 63
11/15/08 21
10/16/08 78
10/9/08 45
12/5/08 34
11/7/08 26
11/11/08 64
9/2/08 74
9/19/08 57
9/4/08 36
10/8/08 25
10/14/08 55
11/8/08 24
10/5/08 32
10/6/08 74
10/7/08 65
10/15/08 52
12/3/08 22

Notice the data is not even sorted. We want the sum of the November
data
only.

=SUMPRODUCT((MONTH(A1:A30)=11)*(B1:B30))

To get October data use 10 in place of 11
--
Gary''s Student - gsnu200820


"Kesbutler" wrote:

I need to create a formula based on date. I need to creat a calcualte
to
include other cells only if they meet certain date requirements. The
cells
will meet the requirements a certain period of time then will need to
be
removed from the formula. I am trying t automate the worksheet so I
don't
have to update it once a month to exclude the previous 4 weeks. How
can I do
this?