First day of month

[Dianne Groom]

A cell in an Excel workbook contains a date. I want to write macro code to
generate, in another cell, the date of the first day of the next month.



I was going to use the Excel EOMONTH function, but Excel 2002, does not seem
to support the function. I can generate the date using the DATE function,
and I tried using Application.WorksheetFunction in my macro, but it did not
work with the Excel DATE function.



Any suggestions for macro code that will generate the first day of the next
month would be appreciated.

 

[tomek gomek]

=EOMONTH(C4;0)+1
where C4 is your date ? format it of course as you wish

tomek

 

[tomek gomek]

ups.
Analysis ToolPack must be on

tomek

=EOMONTH(C4;0)+1
where C4 is your date ? format it of course as you wish

tomek

 

[NickHK]

Assuming you have a date in A1, in another cell enter:

=DATE(YEAR(A1),MONTH(A1)+1,1)

VBA is similar, but use DateSerial instead of Date.

As for EOMONTH, this is supplied by the Analysis Tool Pack add-in, which may
not be installed. Check Tools>Addins in Excel.

NickHK

 

[Guest]

Try the below code

Dim mDate As Date
Dim mStr
mDate = DateAdd("m", 1, Range("L3").Value)
mStr = "01-" & Format(mDate, "mmm-yy")
Range("M3").Value = Format(mStr, "dd-mmm-yy")

Hope this helps!!

 

[Ron Rosenfeld]

A cell in an Excel workbook contains a date. I want to write macro code to
generate, in another cell, the date of the first day of the next month.



I was going to use the Excel EOMONTH function, but Excel 2002, does not seem
to support the function. I can generate the date using the DATE function,
and I tried using Application.WorksheetFunction in my macro, but it did not
work with the Excel DATE function.



Any suggestions for macro code that will generate the first day of the next
month would be appreciated.

Sub FirstOfMonth()
Dim src As Range
Dim dest As Range

Set src = Range("A1")
Set dest = Range("A2")

If IsDate(src.Text) Then
dest.Value = src.Value - Day(src.Value) + 1
End If

End Sub

--ron

 

[Harlan Grove]

....
Sub FirstOfMonth() ....
If IsDate(src.Text) Then
dest.Value = src.Value - Day(src.Value) + 1
End If

End Sub

That sets dest to the first day of the same month as src. For the next
month, it requires something like

dest.Value = src.Value - Day(src.Value) + 33 _
- Day(src.Value - Day(src.Value) + 32)

 

[Ron Rosenfeld]

That sets dest to the first day of the same month as src. For the next
month, it requires something like

dest.Value = src.Value - Day(src.Value) + 33 _
- Day(src.Value - Day(src.Value) + 32)

<Sigh>. Thanks. I misread the question.
--ron