#1   Report Post  
G
 
Posts: n/a
Default 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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Myrna Larson
 
Posts: n/a
Default

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   Report Post  
G
 
Posts: n/a
Default

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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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

--
Regards
Frank Kabel
Frankfurt, Germany

"G" schrieb im Newsbeitrag
...
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   Report Post  
G
 
Posts: n/a
Default

Thanks Myrna ,

Very helpful

"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   Report Post  
G
 
Posts: n/a
Default

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
...
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   Report Post  
Myrna Larson
 
Posts: n/a
Default

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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date updates from worksheet to chart & changes date to a date series! Help!! Jayjg Charts and Charting in Excel 2 January 22nd 05 04:00 PM
Date issue between Windows and Macintosh version dlg1967 Excel Discussion (Misc queries) 4 January 19th 05 04:51 PM
Cell / Date Formatting Question when result = -0- seve Excel Discussion (Misc queries) 3 January 17th 05 03:29 AM
how do i make a date change automatically if i change one before . dpl7579 Excel Discussion (Misc queries) 1 January 11th 05 10:21 PM
Addition to Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 9th 04 03:06 AM


All times are GMT +1. The time now is 11:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"