PDA

View Full Version : [SOLVED] named range cell counting for calendar miss counting december



mperrah
08-26-2015, 04:00 PM
14267
I have this calendar thanks to SamT.
It counts the cells to fill in day of the month numbers in worksheet change using several functions (very elegant)
For some reason it stops on the 12th day for december.
I tried different years and they all stop on the 12th
The other months of the year work perfectly to the end date for each month.
Kinda stumped.
thank you for any help.

-mark

SamT
08-26-2015, 06:24 PM
Function EOM(dDate As Date) As Date
dDate = Format(dDate, "d-m-yyyy")
EOM = DateAdd("d", -1, DateValue(Month(dDate) + 1 & "-1-" & Year(dDate)))
End Function
Thank you for thoroughly testing that function for me. I wrote it years ago but didn't need it until now.

p45cal
08-27-2015, 04:44 AM
SamT, trying out your function as-is (I'm in the UK where it's d/m/y) for today (27th Aug 2015) with:
Debug.Print Format(EOM(Date), "d mmm yy")
I got:
8 Jan 15

Not sure whether I'm doing as I should, however there is a worksheet function (not EOM which is not available to VBA) EoMonth which you might use; it takes the date you supply and another argument being the number of months before/after your supplied date, in this case:
Application.EoMonth(YourSuppliedDate, 0)

SamT
08-27-2015, 05:58 AM
p45cal,

EoMonth does not seem to be available in Excel 2003.

Try adjusting the Format in EOM to "mmm/dd/yyyy".

Thanks

mperrah
08-27-2015, 07:57 AM
Nailed it, out of the park.

Thank you, SamTerrific

-mark