Formula in excel for a date

Solved
Report
Posts
13
Registration date
Friday March 4, 2016
Status
Member
Last seen
July 6, 2021
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
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

3 replies

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2821 users have said thank you to us this month

Posts
13
Registration date
Friday March 4, 2016
Status
Member
Last seen
July 6, 2021

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.
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Gerberreinette,

You can; select cells E6 to F9 and drag that selection down as far as needed.

Best regards,
Trowa