Date function not accounting for leap year

**SpencerD1985** · 09-18-2013, 11:58 AM

I have a spreadsheet with this year's and previous year's data. I need to have the last day of the month pulled into a specific cell so the rest of my formulas work. Instead of inputting this years date and last years date, I would like to dumb it down so only one date needs to be changed. The formula I am using is =DATE(YEAR(B5),MONTH(B5)-12,DAY(B5)). B5 = Current Month End Date (i.e. 2/29/2012) The issue arises when the current year's date is a leap year (i.e 2/29/2012). This formula pulls in 3/1/2011. I need it to pull in 2/28/2011. I need this formula to work for both leap year and non leap year so the other users don't have to think too hard and do too much work. So if there is something that can be done where it will pull in the last date of the current month please let me know.

**martindwilson** · 09-18-2013, 12:02 PM

use end of month
=EOMONTH(A1,-12)

**SpencerD1985** · 09-18-2013, 12:15 PM

Perfect. Thank you

**arlu1201** · 09-19-2013, 05:48 AM

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.