# Thread: auto date series (97+)

1. ## auto date series (97+)

I need a spreadsheet which displays the days (m/d/yy) of current month starting in cell A3 ending at cell A34?/A33? (depends on # of days in month). I need the data to appear automatically without any user intervention every time a user opens the spreadsheet. Another option might be that the user run a macro after opening the spreadsheet.
Tried to do it myself using function(now) and then strings but then I have trouble with number of days for different months---there must be a more simple solution.
TIA
Smbs

2. ## Re: auto date series (97+)

Smbs

There is an EOMONTH function in the Analysis Toolpack but most people don't use it if the book is shared and other toolpack functions are not used.

Its easy enough with basic functions <img src=/S/compute.gif border=0 alt=compute width=40 height=20>. Put =Now() in a cell - say A1. Then the first day of that month is

=Date(Year(A1),Month(A1),1)

The first day of next month (including December/January) is

=Date(Year(A1),Month(A1)+1,1)

and the last day of the current month is

=Date(Year(A1),Month(A1)+1,0)

(Yes, it works ) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

So - days in the month are First day of next month minus first day of this month

=Date(Year(A1),Month(A1)+1,1) - Date(Year(A1),Month(A1),1)

3. ## Re: auto date series (97+)

Thanx for quick reply--I am now trying to use the formulae that u gave me for amount of days etc but I am getting syntax errors when placing code in vba editor ---"Date(Year(A1),Month(A1)+1,1) - Date(Year(A1),Month(A1),1)" and "Date(Year(A1),Month(A1),1)"-----missing ")"
any ideas????
Thanx
Smbs

4. ## Re: auto date series (97+)

Put this formula in A3:

<pre>=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
</pre>

Then put this formula in A4 and fill it down through A33:

<pre>=IF(ISNUMBER(A3),IF(MONTH(A3+1)=MONTH(A3),A3+ 1,""),"")
</pre>

5. ## Re: auto date series (97+)

Thanx -works like a charm---if u have the patience maybe u would explain formula #2
thanx again
Smbs

6. ## Re: auto date series (97+)

I'll try.

Here is the formula for reference:

<pre>=IF(ISNUMBER(A3),IF(MONTH(A3+1)=MONTH(A3),A3+ 1,""),"")
</pre>

The ISNUMBER(A3) checks to see if the cell above contains a number to avoid a #Value error for the cells past the last day of the month in months that have less than 30 days. If it does not contain a number, then the IF returns a null string ("") to the cell. If it does contain a number, then the second IF checks to see if the date in the previous cell and that date plus 1 day are in the same month. If they are, it returns the previous cell's date plus 1 day. If it does not, then it returns a null string.

7. ## Re: auto date series (97+)

Thanx ---really appreciate it I learn all the time!!
Smbs

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•