Date question

[The Wolf]

If you have a workbook with different sheets for the months. Jan, Feb.
March, etc.

And you use the now or today function is there a way to limit the date
within the month sheet.

Here's my problem =C56/(TODAY()-DATE(2004,4,1)) gives the average for April
(month 4)

But when May starts I want the April sheet to stop at April 30, 2004.

In other words if "Today" or "Now" is May 1, 2004 that would skew my
averages for April.

 

[JE McGimpsey]

One way:

=C56/(MIN(TODAY(),DATE(2004,4,30))-DATE(2004,4,1))

although I would think that

DATE(2004,4,0)

would be more appropriate for the last term. That way an entry on April
1st would return a denominator of 1, not 0. Note that the 0th day of the
month, to XL, is the last day of the previous month.

 

[Bernard Rey]

If I may, I'd like to add a little correction to the otherwise excellent
formula. In fact referring to the first day of the month will result in a
one day too short period.

Ie today being the 19th, if you write TODAY()-DATE(2004,4,1) the result will
be "18" and not "19" as I guess you expect.

So you should rather write:

=C56/(MIN(TODAY(),DATE(2004,4,30))-DATE(2004,4,0))

Or of course (strictly equivalent):

=C56/(MIN(TODAY(),DATE(2004,4,30))-DATE(2004,3,31))



JE McGimpsey wrote :

 

[TH]

You could do this:

=C56/(MAX(TODAY(),DATE(2004,4,30))-DATE(2004,4,1))

TH

 

[The Wolf]

If I may, I'd like to add a little correction to the otherwise excellent
formula. In fact referring to the first day of the month will result in a
one day too short period.

Ie today being the 19th, if you write TODAY()-DATE(2004,4,1) the result will
be "18" and not "19" as I guess you expect.
Thanks!


So you should rather write:

=C56/(MIN(TODAY(),DATE(2004,4,30))-DATE(2004,4,0))

Or of course (strictly equivalent):

=C56/(MIN(TODAY(),DATE(2004,4,30))-DATE(2004,3,31))



JE McGimpsey wrote :