Column of days and dates for a given month

**johnfar** · 10-28-2011, 12:33 PM

Hi there,

I am trying to achieve the following.
In cell A1 say, I will have a drop down list for months of the current year. Lets choose October for our example.

IN cell A2 I would like to show the date of the first day of that month. i.e. 01/10/2011 and
in A3 I require the second date of that month ie 02/10/2011. A4 will contain 3rd date and so on until the last day of the month.
Now when I change the month from the drop down list, all the dates in colum A change automatically even though some months have a diffrent number of days.

ANy help would be appreciated. Thanks

**protonLeah** · 10-28-2011, 04:42 PM

With the Month in A1,
A2:A32=

Please Login or Register  to view this content.

**daddylonglegs** · 10-28-2011, 05:02 PM

For current calendar year you can use this formula in A2

=IFERROR((ROWS(A$2:A2)&A$1)+0,"")

format in required date format, i.e. dd/mm/yyyy and copy down to A32. In shorter months like February you'll get blanks in the latter cells

**ChemistB** · 10-28-2011, 05:02 PM

Another option
In A2

Please Login or Register  to view this content.

In A3:A32

Please Login or Register  to view this content.

This assumes the original month is text, not a number formatted as a month (that would make things easier)

**johnfar** · 10-28-2011, 07:41 PM

Originally Posted by daddylonglegs

For current calendar year you can use this formula in A2

=IFERROR((ROWS(A$2:A2)&A$1)+0,"")

format in required date format, i.e. dd/mm/yyyy and copy down to A32. In shorter months like February you'll get blanks in the latter cells

Thanks for all the suggestions. This one appears most elegant. Struggling to understand how it works...I'm a bit of a newbie when it comes to excel. Can you break it down with some explanation pls.

Thanks

**daddylonglegs** · 10-29-2011, 05:24 AM

ROWS(A$2:A2) gives a count of the rows in that range, which is 1. When you copy that down it increments every row (because the first A2 is fixed with $ and the second isn't), so you get 1 in A2, 2 in A3, 3 in A4 etc....

ROWS is more robust than using ROW, especially if you add or delete rows above the data

So with November in A1 with this part

ROWS(A$2:A2)&A$1

You are simply "concatenating" the number to the month, so you get "1November" in A2 "2November" in A3 etc. which is still text

Now if you perform any mathematical operation on that text (preferably one that doesn't change the value like adding zero, multiplying by 1 etc.) then the text is "co-erced" to the equivalent date (in the current year) so when we use

(ROWS(A$2:A2)&A$1)+0

That is the equivalent of "1November"+0.....which converts to a valid date 1-Nov-2011.

When you get down to A32, 31November isn't a valid date and excel knows that so when you add zero you get an error. IFERROR function is used to convert that error to a blank [""]

**johnfar** · 11-23-2011, 12:22 AM

Many thanks.

I opened the above in excel 97-2003 and the IFERROR was not recognised. Can the above be modified using the ISERROR function so that its backward compatible with excel 97-2003?