For example, to determine the serial date number for the last day of the current month, use
=EOMONTH(NOW(),0)
To avoid using the EOMONTH formula, you can use the fact that the zeroth day of the next month is the last day of the current month:
=DATE(YEAR(NOW()),MONTH(NOW())+1,0)
There are many ways to do this, such as subtracting a day from the first day of the next month:
=DATEVALUE((MONTH(NOW())+1)&"/1/"&YEAR(NOW()))-1
Of course, NOW() could be replaced with a reference to a date cell, or in the case of the last formula ...
=DATEVALUE((month+1)&"/1/"&year)-1where month and year are named references to cells.
3 comments:
Dude, nice EOMonth explanation
Great post, very helpful in automating some of our monthly shipment tracking spreadsheets. Thank you!
Thanks for this very clear and straight forward explanation. Much appreciated
Post a Comment