Formula in excel for a date Solved/Closed

Question

Hello, 
Hope you are well. Please assist me with the date calculation in Column E and Column F. The 2012/02/29 date should show (29) because for tax purposes it is a leap year. The leap years are 2012, 2016, 2020, 2024, 2028,2032,2036. The FYS for all taxpayers will start on 2010/03/01 and that specific tax year will end on 2011/02/28. The next tax year will start on 2011/03/01 and ends on 2012/02/28 + 1 to accommodate leap year.
Thank you in advance.


Column A, Column B, Column C, Column D, Column E, Column F
FYS, 2010/03/01​, FYE, 2011/02/28, Calc. period from, 	Calculate to
2010​, 10H1, 1st Estimate, FYS + 6 Months, 2010/03/01​, 2010/08/31​
2010, 10H2, 2nd Estimate, FYS + 12 Months, 2010/09/01​, 2011/02/28​
2010, 2K10, ROE, FYS + 18 Months, 2010/03/01​, 2011/08/31​
2010​, 2K10, Final, FYS + 24 Months, 2010/03/01​, 2012/02/29​
2011​, 11H1, 1st Estimate, FYS + 6 Months, 2011/03/01​, 2011/08/31
2011​, 11H2, 2nd Estimate, FYS + 12 Months, 2011/09/01​, 2012/02/29
2011​, 2K11, ROE, FYS + 18 Months, 2011/03/01​, 2012/08/31
2011​, 2K11, Final, FYS + 24 Months, 2011/03/01​, 2013/02/28

TrowaD · Answer

Hi Gerberreinette,

Here are the requested formula's per cell:

E2: Enter you start date here: 2010/03/01
F2: =DATE(YEAR(E2),MONTH(E2)+6,DAY(E2)-1)
E3: =F2+1
F3: =DATE(YEAR(E3),MONTH(E3)+6,DAY(E3)-1)
E4: =E2
F4: =DATE(YEAR(E4),MONTH(E4)+18,DAY(E4)-1)
E5: =E2
F5: =DATE(YEAR(E5),MONTH(E5)+24,DAY(E5)-1)
E6: =DATE(YEAR(E2)+1,MONTH(E2),DAY(E2))
F6: =DATE(YEAR(E6),MONTH(E6)+6,DAY(E6)-1)
E7: =F6+1
F7: =DATE(YEAR(E7),MONTH(E7)+6,DAY(E7)-1)
E8: =E6
F8: =DATE(YEAR(E8),MONTH(E8)+18,DAY(E8)-1)
E9: =E6
F9: =DATE(YEAR(E9),MONTH(E9)+24,DAY(E9)-1) 

Best regards,
Trowa

Gerberreinette · Answer

Hi Trowa
Thank you so much this is amazing. 
One question.
Is there maybe a way to enter a formula that I can just drag down to the rest of the years? I need to do it for FYS 2010 to 2025.
Thank you for the help I appreciate this so much.

TrowaD · Answer

Hi Gerberreinette, 

You can; select cells E6 to F9 and drag that selection down as far as needed.

Best regards,
Trowa

Formula in excel for a date

3 responses

Similar discussions

Subscribe To Our Newsletter!