Excel 2007 : Automatically creating a series of dates

**marketshare** · 02-10-2010, 12:42 AM

I would like to create a template. This template would have 2 input cells - one to enter a month and one to enter a year (e.g. February and 2000).

From these 2 cells I would like to automatically create a column of dates, from the 1st of that month through to the last day of that month (e.g. 1st Feb 2000, 2nd Feb 2000, through to 29th Feb 2000).

I've managed to create the first day of the month using DATE and the last day of the month using EOMONTH. But this doesn't really get me where I need to go! Any ideas please?

EXAMPLE:
Enter month: 2
Enter year: 2000

1/02/2000
2/02/2000
3/02/2000
4/02/2000
5/02/2000
6/02/2000
7/02/2000
8/02/2000
9/02/2000
10/02/2000
11/02/2000
12/02/2000
13/02/2000
14/02/2000
15/02/2000
16/02/2000
17/02/2000
18/02/2000
19/02/2000
20/02/2000
21/02/2000
22/02/2000
23/02/2000
24/02/2000
25/02/2000
26/02/2000
27/02/2000
28/02/2000
29/02/2000

**JBeaucaire** · 02-10-2010, 03:59 AM

Cell B2: month value (user)
Cell B3: year value (user)

Cell A5: =DATE(B2,B1,1)
Cell A6: =A5+1

Copy cell A6 down through cell A32. You now have the dates through the 28th of the month.

Cell A33: =IF(A32="","",IF(MONTH(A32+1)>MONTH(A32),"",A32+1))

Copy that down through A35.

Now your monthly list of dates will create itself accurately.

**DonkeyOte** · 02-10-2010, 04:08 AM

If you prefer a consistent formula for all perhaps:

A5: =IF(ROWS(A$5:A5)>DAY(DATE($B$3,$B$2+1,0)),"",DATE($B$3,$B$2,ROWS(A$5:A5)))
copied down to A35

above uses same ranges as outlined in JB's prior post

**marketshare** · 02-10-2010, 09:36 PM

Great. That works well. Thank you.

Another question: if I enter the name of the month e.g. February, is there an easy way of converting this to its numeric equivalent e.g. 2 (without using IFs)?

**JBeaucaire** · 02-10-2010, 10:08 PM

(groan)

You have a working solution and you just have to go and get all humanized on the cell value, huh? Hehe... Your original example is perfect!

Anyway...using Don's 1-formula approach...in A5:

=IF(ROWS(A$5:A5) > DAY(EOMONTH(DATEVALUE("1 " & $B$2 & " " & $B$3), 0)), "", DATEVALUE(ROWS($A$5:$A5) & $B$2 & " " & $B$3))