Calculating Last day of month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having a problem with calculating the last day of the current month in excel. If I use the EOMONTH function with parameters NOW() and 0 i.e. EOMONTH(NOW(), 0) in the cell where I want the date to go everything works fine. However
I want to put the EOMONTH into a macro at which point the compiler complain the EOMONTH is not a defined sub or function. Are there any VB functions that will return the last day of the current month

thank you in advanced

Jon
 
Jon,

A cool behaviour of DateSerial is that it accepts values outside the bounds
of a calendar month/day. This means you can choose month 13, which is
interpreted as month 12 + 1


dtmTemp = Now()
dtmTemp = DateSerial(Year(dtmTemp), Month(dtmTemp) + 1, 1) - 1

Rob


Jon said:
I am having a problem with calculating the last day of the current month
Click to expand...
in excel. If I use the EOMONTH function with parameters NOW() and 0 i.e.
EOMONTH(NOW(), 0) in the cell where I want the date to go everything works
fine. However
I want to put the EOMONTH into a macro at which point the compiler
Click to expand...
complain the EOMONTH is not a defined sub or function. Are there any VB
functions that will return the last day of the current month
 
Jon

In the VBE, go Tools, References and select the atpvbaen.xls option. EOMONTH should work.


Tony

----- Jon wrote: -----

I am having a problem with calculating the last day of the current month in excel. If I use the EOMONTH function with parameters NOW() and 0 i.e. EOMONTH(NOW(), 0) in the cell where I want the date to go everything works fine. However
I want to put the EOMONTH into a macro at which point the compiler complain the EOMONTH is not a defined sub or function. Are there any VB functions that will return the last day of the current month

thank you in advanced

Jon
 
Jon,

Along the same lines as Rob, but a little shorter


myDate = DateSerial(Year(Date), Month(Date) + 1, 0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top