Range of Days in a month
 

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.

Range of Days in a month
 

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

Range of Days in a month
 

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 -


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

Range of Days in a month
 

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

Range of Days in a month
 

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.

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

Rick

Range of Days in a month
 

Here's one way:
http://www.dailydoseofexcel.com/arch...ugly-formulas/

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

NickHK

wrote in message
ups.com...
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.

Range of Days in a month
 

On Jun 25, 11:17 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
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.

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.

Range of Days in a month
 

On Mon, 25 Jun 2007 19:55:53 -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 -


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

Range of Days in a month
 

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.

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

Range of Days in a month
 

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

Range of Days in a month
 

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