Range of Days in a month

[joevisco]

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.

 

[Ron Rosenfeld]

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

 

[saphite]

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 -

Thanks for the response Ron.

The first and second rows worked and gave me Jun 1st and Jun 2nd. But
the rest (3 - 31) also come up as June 2nd.


I

 

[saphite]

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

 

[Rick Rothstein \(MVP - VB\)]

Here's one possible way:

Thanks for the response Ron.

The first and second rows worked and gave me Jun 1st and Jun 2nd. But
the rest (3 - 31) also come up as June 2nd.

Check what you did again... it worked fine for me.

Rick

 

[NickHK]

Here's one way:
http://www.dailydoseofexcel.com/archives/2004/06/23/ugly-formulas/

Note there's a slight typo in it; change "" to "".

NickHK

 

[saphite]

Check what you did again... it worked fine for me.

Rick

The answer is that my workbook was not doing calculations, but
manually.

Ron, I am in awe. Thanks again!

BTW, that second formula is definitely ugly.

 

[Ron Rosenfeld]

Thanks for the response Ron.

The first and second rows worked and gave me Jun 1st and Jun 2nd. But
the rest (3 - 31) also come up as June 2nd.


I

Most likely explanations: Either you did not enter the formula in A2 as I
posted it, or you did not use the Excel Fill Down command.

Check to see that the ROWS($1:1) is entered properly with the "$" before the
first "1".

Check that $A$1 is entered with the "$"'s.

Select A2:A31. Then Edit/Fill/Down.
--ron

 

[Rick Rothstein \(MVP - VB\)]

I am trying to list the dates in the current month (from the 1st to

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

Here is a slightly shorter formula that can be used in A2 (filled down to
A31)...

A2: =IF((MONTH(IF(A1="",0,A1)+1)=MONTH($A$1)),A1+1,"")

The formula in A1 stays the same.

Rick

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

Here is a slightly shorter formula that can be used in A2 (filled

down to A31)...

A2: =IF((MONTH(IF(A1="",0,A1)+1)=MONTH($A$1)),A1+1,"")

Even shorter...

A2: =IF(MONTH(N(A1)+1)=MONTH($A$1),A1+1,"")

Rick