Remember Me?

#1
October 29th 04, 05:42 AM
 G Posts: n/a
Date Question

I have an Excel SS that has a summary page that has several count if / sum if
values. Many of which refer to date ranges. today etc.

Is it possible for me to capture all data in a range of the last calander
month ie. if i all dates in October.

Currently i have the formulas written to count details within the last 31
days however i would like to make this a little more correct.

example :
=SUMPRODUCT((whoC="gn") * (typec="email " ) * (whenb<B1) * ( whenb B2) )

B1 = Now()
B2 = B1 - 31

Any help very much appreciated

#2
October 29th 04, 06:26 AM
 Frank Kabel Posts: n/a

Hi
try:
B1:
=DATE(YEAR(NOW()),MONTH(NOW())-1,1)

B2:
=DATE(YEAR(NOW(),MONTH(NOW()),0)

--
Regards
Frank Kabel
Frankfurt, Germany

"G" schrieb im Newsbeitrag
...
I have an Excel SS that has a summary page that has several count if

/ sum if
values. Many of which refer to date ranges. today etc.

Is it possible for me to capture all data in a range of the last

calander
month ie. if i all dates in October.

Currently i have the formulas written to count details within the

last 31
days however i would like to make this a little more correct.

example :
=SUMPRODUCT((whoC="gn") * (typec="email " ) * (whenb<B1) * ( whenb
B2) )

B1 = Now()
B2 = B1 - 31

Any help very much appreciated

#3
October 29th 04, 06:36 AM
 Myrna Larson Posts: n/a

In B1 put the starting date, i.e. 10/1/2004. In B2, manually enter the end
date, 10/31/2004, or use a formula like =DATE(YEAR(B1),MONTH(B1)+1,0) which
will generate the last day of that month, 10/31/2004, for you.

Then the formula is

=SUMPRODUCT((whoC="gn") * (typec="email " ) * (whenb=B1) * ( whenb<=B2) )

On Thu, 28 Oct 2004 21:42:01 -0700, "G" wrote:

I have an Excel SS that has a summary page that has several count if / sum if
values. Many of which refer to date ranges. today etc.

Is it possible for me to capture all data in a range of the last calander
month ie. if i all dates in October.

Currently i have the formulas written to count details within the last 31
days however i would like to make this a little more correct.

example :
=SUMPRODUCT((whoC="gn") * (typec="email " ) * (whenb<B1) * ( whenb B2) )

B1 = Now()
B2 = B1 - 31

Any help very much appreciated

#4
October 29th 04, 06:37 AM
 G Posts: n/a

Thanks Frank ,

I am having a little trouble with the second formula (b2)..

Any thoughts

"Frank Kabel" wrote:

Hi
try:
B1:
=DATE(YEAR(NOW()),MONTH(NOW())-1,1)

B2:
=DATE(YEAR(NOW(),MONTH(NOW()),0)

--
Regards
Frank Kabel
Frankfurt, Germany

"G" schrieb im Newsbeitrag
...
I have an Excel SS that has a summary page that has several count if

/ sum if
values. Many of which refer to date ranges. today etc.

Is it possible for me to capture all data in a range of the last

calander
month ie. if i all dates in October.

Currently i have the formulas written to count details within the

last 31
days however i would like to make this a little more correct.

example :
=SUMPRODUCT((whoC="gn") * (typec="email " ) * (whenb<B1) * ( whenb
B2) )

B1 = Now()
B2 = B1 - 31

Any help very much appreciated

#5
October 29th 04, 06:40 AM
 Frank Kabel Posts: n/a

Hi
typo on my side. Try:
=DATE(YEAR(NOW()),MONTH(NOW()),0)

--
Regards
Frank Kabel
Frankfurt, Germany

"G" schrieb im Newsbeitrag
news
Thanks Frank ,

I am having a little trouble with the second formula (b2)..

Any thoughts

"Frank Kabel" wrote:

Hi
try:
B1:
=DATE(YEAR(NOW()),MONTH(NOW())-1,1)

B2:
=DATE(YEAR(NOW(),MONTH(NOW()),0)

--
Regards
Frank Kabel
Frankfurt, Germany

"G" schrieb im Newsbeitrag
...
I have an Excel SS that has a summary page that has several count

if
/ sum if
values. Many of which refer to date ranges. today etc.

Is it possible for me to capture all data in a range of the last

calander
month ie. if i all dates in October.

Currently i have the formulas written to count details within the

last 31
days however i would like to make this a little more correct.

example :
=SUMPRODUCT((whoC="gn") * (typec="email " ) * (whenb<B1) * (

whenb
B2) )

B1 = Now()
B2 = B1 - 31

Any help very much appreciated

#6
October 29th 04, 06:55 AM
 G Posts: n/a

Thanks Myrna ,

"Myrna Larson" wrote:

In B1 put the starting date, i.e. 10/1/2004. In B2, manually enter the end
date, 10/31/2004, or use a formula like =DATE(YEAR(B1),MONTH(B1)+1,0) which
will generate the last day of that month, 10/31/2004, for you.

Then the formula is

=SUMPRODUCT((whoC="gn") * (typec="email " ) * (whenb=B1) * ( whenb<=B2) )

On Thu, 28 Oct 2004 21:42:01 -0700, "G" wrote:

I have an Excel SS that has a summary page that has several count if / sum if
values. Many of which refer to date ranges. today etc.

Is it possible for me to capture all data in a range of the last calander
month ie. if i all dates in October.

Currently i have the formulas written to count details within the last 31
days however i would like to make this a little more correct.

example :
=SUMPRODUCT((whoC="gn") * (typec="email " ) * (whenb<B1) * ( whenb B2) )

B1 = Now()
B2 = B1 - 31

Any help very much appreciated

#7
October 29th 04, 06:56 AM
 G Posts: n/a

Thanks Frank,

I actually worked it out about 3 minutes after posting my reply.. my fault i
am a novice when it comes to date functions..

I don't suppose there is an easy way to work the same equasion out but for
last calander week ??

Again Thanks

"Frank Kabel" wrote:

Hi
typo on my side. Try:
=DATE(YEAR(NOW()),MONTH(NOW()),0)

--
Regards
Frank Kabel
Frankfurt, Germany

"G" schrieb im Newsbeitrag
news
Thanks Frank ,

I am having a little trouble with the second formula (b2)..

Any thoughts

"Frank Kabel" wrote:

Hi
try:
B1:
=DATE(YEAR(NOW()),MONTH(NOW())-1,1)

B2:
=DATE(YEAR(NOW(),MONTH(NOW()),0)

--
Regards
Frank Kabel
Frankfurt, Germany

"G" schrieb im Newsbeitrag
...
I have an Excel SS that has a summary page that has several count

if
/ sum if
values. Many of which refer to date ranges. today etc.

Is it possible for me to capture all data in a range of the last
calander
month ie. if i all dates in October.

Currently i have the formulas written to count details within the
last 31
days however i would like to make this a little more correct.

example :
=SUMPRODUCT((whoC="gn") * (typec="email " ) * (whenb<B1) * (

whenb
B2) )

B1 = Now()
B2 = B1 - 31

Any help very much appreciated

#8
October 29th 04, 07:55 AM
 Myrna Larson Posts: n/a

Assuming the calendar week starts on Sunday, this formula starts with the
current date, backs up to the previous Saturday, then backs up another 6 days
to the Sunday before the previous Saturday. If the current date is a Saturday,
it will back up a total of 13 days.

=TODAY()-WEEKDAY(TODAY())-6

The end of that week is that date + 6.

On Thu, 28 Oct 2004 22:56:01 -0700, "G" wrote:

Thanks Frank,

I actually worked it out about 3 minutes after posting my reply.. my fault i
am a novice when it comes to date functions..

I don't suppose there is an easy way to work the same equasion out but for
last calander week ??

Again Thanks

"Frank Kabel" wrote:

Hi
typo on my side. Try:
=DATE(YEAR(NOW()),MONTH(NOW()),0)

--
Regards
Frank Kabel
Frankfurt, Germany

"G" schrieb im Newsbeitrag
news
Thanks Frank ,

I am having a little trouble with the second formula (b2)..

Any thoughts

"Frank Kabel" wrote:

Hi
try:
B1:
=DATE(YEAR(NOW()),MONTH(NOW())-1,1)

B2:
=DATE(YEAR(NOW(),MONTH(NOW()),0)

--
Regards
Frank Kabel
Frankfurt, Germany

"G" schrieb im Newsbeitrag
...
I have an Excel SS that has a summary page that has several count

if
/ sum if
values. Many of which refer to date ranges. today etc.

Is it possible for me to capture all data in a range of the last
calander
month ie. if i all dates in October.

Currently i have the formulas written to count details within the
last 31
days however i would like to make this a little more correct.

example :
=SUMPRODUCT((whoC="gn") * (typec="email " ) * (whenb<B1) * (

whenb
B2) )

B1 = Now()
B2 = B1 - 31

Any help very much appreciated

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Jayjg Charts and Charting in Excel 2 January 22nd 05 04:00 PM dlg1967 Excel Discussion (Misc queries) 4 January 19th 05 04:51 PM seve Excel Discussion (Misc queries) 3 January 17th 05 03:29 AM dpl7579 Excel Discussion (Misc queries) 1 January 11th 05 10:21 PM Rich New Users to Excel 2 December 9th 04 03:06 AM

All times are GMT +1. The time now is 05:04 PM.