On Mon, 25 Jun 2007 20:14:02 -0700, saphite wrote:

On Jun 25, 10:32 pm, Ron Rosenfeld wrote:
On Mon, 25 Jun 2007 19:15:46 -0700, wrote:
I am trying to list the dates in the current month (from the 1st to
the 28th, or 29th, or 30th, or 31st, depending on the month), in a
range of cells, one day per cell.

Is it me, or are the date functions in excel way too complicated?

I've experimented with the Now() and Today() functions, with no luck.

Can someone give me an idea on how to do this?

I'd prefer not to use a macro, because it is not something i want to
have to manually start every time I open the spreadsheet.

Any clues would be greatly appreciated.

Thanks.

Here's one possible way:

A1: =DATE(YEAR(TODAY()),MONTH(TODAY()),1)
A2: =IF(MONTH($A$1+ROWS($1:1))=MONTH($A$1),$A$1+ROWS($ 1:1),"")

Fill down to A31

--ron- Hide quoted text -

- Show quoted text -

So cool!

Here's something odd though:

I dragged and filled 3-31 and they all came out as Jun 2nd.

I then changed the number in the second ROWS statement to +1, and then
back to the original.

The dates then incremented correctly.

Thanks ron!

Maybe if you have time you can explain exactly why that works, so I
can learn?


Joe

Dates are stored as serial numbers.

So to increment by one day, one adds "1".

I chose to use A1 as the index, and add 1,2,3... to that value.

That number to add is computed by the ROWS() function.

If you examine the formulas in the subsequent cells to A2, you will see that
the ROWS function becomes ROWS($1:2) ROWS($1:3) and so forth.
ROWS($1:2)--2.

Hope that's enough of an explanation, but if not, post back.
--ron