Excel 2002: How to make a date run for a month only ?

[Mr. Low]

Dear Sir,

May I know how to make a date run within a cell on daily basis up to the
last day of the month using Excel formulas ?

I used =TODAY( ) , but it runs beyond at the last day of the month.

I would be glad if you could help me on this.


Thanks

Low

 

[Roger Govier]

Hi

Not sure whether I am interpreting your request correctly.

Assuming your start date for the month is in cell A1
in cell A2 enter
=IF(MONTH(A1+1)>MONTH(A1),"",A1+1)
Copy down through cells A3:A31

If you enter 01 Jan 08 in A1, all 31 cells will be filled.
If you enter 01 Feb 08 in A1, cells A30 and A31 will be blank

 

[Pete_UK]

How would Excel know which month you are talking about?

If you were to put a date in A1, for example 01/01/08, then you could
have something like this:

=IF(AND(MONTH(A1)=MONTH(TODAY()),YEAR(A1)=YEAR(TODAY()),TODAY(),"")

Will return today's date only if it is in the same month and year as
the date in A1, otherwise it will return a blank.

Hope this helps.

Pete

 

[Ron Rosenfeld]

I used =TODAY( ) , but it runs beyond at the last day of the month.

It would be helpful if you would post the formula you are actually using.
--ron

 

[Pete_UK]

Sorry, missed a bracket out:

=IF(AND(MONTH(A1)=MONTH(TODAY()),YEAR(A1)=YEAR(TODAY())),TODAY(),"")

Hope this helps.

Pete

 

[Mr. Low]

Hello Roger,

Thanks for your reply.

Actually I want only one cell to update itself from first day of the month
to the last day of the month.

Eg: on 1.2.2008

A
1 1/2/2008

On 2.2.2008
A
1 2/2/2008


On 29.2.2008
A
1 29/2/200


On 5.3.2008
A
1 29/2/2008

The date changes automatically as the day passed by but end at the last day
of the month.

I hope you can get what I mean.

Thanks

Low