For a month number in column A, I want the LAST day of that particular month in column B. Right now, I'm working with year 2014, so:

A1 number is 1 so B1 date is 01/31/14
A2 number is 2 so B2 date is 02/28/14
A3 number is 11 so B3 date is 11/30/14
A4 number is 10 so B4 date is 10/31/14

etc. for all twelve months of the year 2014. I will eventually need the same for year 2015, 2016 etc.


Formula for B1 (copy down the column):


This formula returns the date one day before the first day of the next month, which will be the last day of the specified month.

If you will be reusing the same data, stick the year in a cell somewhere and reference that cell instead of hard coding the year in the formula; just change the year in the reference cell. For example, put 2014 in cell C1. The formula would then be =DATE($C$1,A1+1,0). For 2015, change C1 to 2015 and all of the formulas will reflect that year.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.