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.

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),"",A 1+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!!


"Sunnyskies" wrote:

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.

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

"Sunnyskies" wrote:

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.

24 degress and bloody barmy.

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

"Mike H" wrote:

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

"Sunnyskies" wrote:

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.

Thanks Pranav

"Pranav Vaidya" wrote:

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),"",A 1+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!!


"Sunnyskies" wrote:

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.