List of dates for month from start date

S

Sunnyskies

Morning from Sunny RSA,

In cell A1 I have a start date eg.2007/08/01. This will be the start date
which will be inputted, thus it does not necessary have to be the first of
the month.
In cell A2 and down to whenever I want a formula to provide a date (in each
cell) until the end of the month of the start date as in cell A1.
The 1st of the next month must not show.

Waiting in anticipation.
 
P

Pranav Vaidya

Assume that your start date is in cell A1.

Then enter the below formula from cell A2
=IF(ISERROR(A1+1),"",IF(MONTH(A1+1)>MONTH(A1),"",A1+1))

Now drag this formula to cell A31.

This works, I have tested.
 
M

Mike H

Morning from the (un)sunny UK. Try this:-

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1"
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Range("A2:A31").ClearContents
daysinmonth = Range("B1").Value 'hide this away somewhere
myday = Day(Range("A1").Value)
Set myrange = Range("A1:A" & (daysinmonth - myday + 1))
myrange.Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlChronological,
Date:= _
xlDay, Step:=1, Trend:=False
End If
ws_exit:
Application.EnableEvents = True
End Sub

The routine use the formula in B1
=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)
You can put this somewhere out of the way but don't forget to change the
code to tell it where it is.

Mike
 
S

Sunnyskies

24 degress and bloody barmy.

Thanks Mike, but trying to stay away from scripting and macros.
 
S

Sunnyskies

Thanks Pranav

Pranav Vaidya said:
Assume that your start date is in cell A1.

Then enter the below formula from cell A2
=IF(ISERROR(A1+1),"",IF(MONTH(A1+1)>MONTH(A1),"",A1+1))

Now drag this formula to cell A31.

This works, I have tested.
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!
Click to expand...
 
Top