Question: Calculate the last day of a month?

Answer: We need a starting date, in this case 2007-01-01. To calculate the last day of the starting month we use this formula: =DATE(YEAR(A2),MONTH(A2)+1,0).

So a zero in DATE(year,month,0) returns the last day in the previous month, that is the trick!

YEAR(serial_number) returns the year of a date, an integer of the range 1900-9999

MONTH(serial_number) returns the month, a number from 1 (January) to 12 (December

DATE(year,month,day) returns the number that represents the datein Microsoft Office Excel date-time code