Date Formula

[R31n3tt3]

Good day
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

 

[Joe4]

Can you please explain where the starting year and month are supposed to come from for the column E and F calculations?
Which cells should it be looking at?
I don't see why/how some starting dates are month 3 and others are month 9.

 

[Marcelo Branco]

Not sure i understand what you're looking for...

See if this does what you want

05072021 Testes.xlsx
	A	B	C	D	E	F
1	FYS	2010/03/01	FYE	28/02/2011	Calc. period from	Calculate to
2	2010	10H1	1St Estimate	FYS + 6 Months	01/03/2010	2010/08/31
3	2010	10H2	2nd Estimate	FYS + 12 Months	01/03/2010	2011/02/28
4	2010	2K10	ROE	FYS + 18 Months	01/03/2010	2011/08/31
5	2010	2K10	Final	FYS + 24 Months	01/03/2010	2012/02/29
6	2011	11H1	1St Estimate	FYS + 6 Months	01/03/2011	2011/08/31
7	2011	11H2	2nd Estimate	FYS + 12 Months	01/03/2011	2012/02/29
8	2011	2K11	ROE	FYS + 18 Months	01/03/2011	2012/08/31
9	2011	2K11	Final	FYS + 24 Months	01/03/2011	2013/02/28
Plan4

Cell Formulas
Range	Formula
E2:E9	E2	=DATE(A2,3,1)
F2:F9	F2	=EDATE(E2,--SUBSTITUTE(MID(D2,SEARCH("+",D2)+1,99),"Months",""))-1

M.

 

[R31n3tt3]

Good day

Here is a formula in excel for a date, but I need to copy and paste the formula every time a new year starts.
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.

Formula in excel for a date - CCM
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
Reinette

 

[R31n3tt3]

Can you please explain where the starting year and month are supposed to come from for the column E and F calculations?
Which cells should it be looking at?
I don't see why/how some starting dates are month 3 and others are month 9.

Good day

Thank you for the help, I really appreciate the time and effort.
Let me try to explain.

All taxpayers or companies are supposed to have registered by FYS 2010. If they registered then we will work on:
FYS is 2010/03/01 and FYE is 2011/02/28
They have 6 months to submit the first declaration 10H1.
They have 12 months to submit the second declaration 10H2.
They have 18 months to submit the ROE declaration 2K10.
They have 24 months to submit the Final declaration 2K10.

For the above, I need to calculate the date of submission for every tax year.

For example. If it is a new company that registered register in 2015 I have to calculate when the company needs to submit the first declaration.
FYS is 2015/03/01 and FYE is 2016/02/28
2010, 2011, 2012, 2013, 2014 the formula will show not applicable.
The calculation will start from 2015 going forward.

I hope this makes sense and that it is clearer for you.
Best regards,
Reinette

 

[Joe4]

I don't understand why two of your entries in column E start with 9/1 instead of 3/1.

Also, you seem to have formulas that are currently working correctly, right?
If so, then it should just be a matter of copying the whole block of formulas.

If you wanted one single formula, then it would be better to have the factors that you need to add to each date in separate columns in a usable format, i.e.
instead of "FYS + 6 Months", you would just have "6" in the column, so it could easily be used in calculations.

 

[Marcelo Branco]

I don't understand either why some entries in column E start in 9/1.

I agree with Joe
- It seems simply a matter of copying the whole block of formulas.
- You should use numeric values 6, 12, 18, 24 to make things easier

And i think that you should
- Use the years in column A in your formulas.
- Use the built-in function EDATE in column F to add months

M.

 

[Dave Patton]

Check Marcelo Branco's suggestion and comments
Try either of the alternatives below

Date and Time 3.xlsm
	A	B	C	D	E	F	G
1	FYS	1-Mar-10	FYE	28-Feb-11	Calc. period from	Calculate to
2	2010	10H1	1st Estimate	FYS + 6 Months	1-Mar-10	31-Aug-10	31-Aug-10
3	2010	10H2	2nd Estimate	FYS + 12 Months	1-Mar-10	28-Feb-11	28-Feb-11
4	2010	2K10	ROE	FYS + 18 Months	1-Mar-10	31-Aug-11	31-Aug-11
5	2010	2K10	Final	FYS + 24 Months	1-Mar-10	29-Feb-12	29-Feb-12
6	2011	11H1	1st Estimate	FYS + 6 Months	1-Mar-11	31-Aug-11	31-Aug-11
7	2011	11H2	2nd Estimate	FYS + 12 Months	1-Mar-11	29-Feb-12	29-Feb-12
8	2011	2K11	ROE	FYS + 18 Months	1-Mar-11	31-Aug-12	31-Aug-12
9	2011	2K11	Final	FYS + 24 Months	1-Mar-11	28-Feb-13	28-Feb-13
1d

Cell Formulas
Range	Formula
E2:E9	E2	=DATE(A2,3,1)
F2:F9	F2	=EDATE(E2,MID(D2,SEARCH("M",D2)-3,2))-1
G2:G9	G2	=EDATE(E2,LOOKUP(LEFT(C2,1),{"1",6;"2",12;"F",24;"R",18}))-1

 

[Marcelo Branco]

If really some entries in column E (the second in the series) must start in 9/1 i suggest

Use a helper column (see column H) - not strictly necessary since the reference to H2 in the formula in F2 could be replaced by the formula in H2

05072021 Testes.xlsx
	A	B	C	D	E	F	G	H
1	FYS	2010/03/01	FYE	2011/02/28	Calc. period from	Calculate to		Helper
2	2010	10H1	1St Estimate	FYS + 6 Months	2010/03/01	2010/08/31		6
3	2010	10H2	2nd Estimate	FYS + 12 Months	2010/09/01	2011/02/28		12
4	2010	2K10	ROE	FYS + 18 Months	2010/03/01	2011/08/31		18
5	2010	2K10	Final	FYS + 24 Months	2010/03/01	2012/02/29		24
6	2011	11H1	1St Estimate	FYS + 6 Months	2011/03/01	2011/08/31		6
7	2011	11H2	2nd Estimate	FYS + 12 Months	2011/09/01	2012/02/29		12
8	2011	2K11	ROE	FYS + 18 Months	2011/03/01	2012/08/31		18
9	2011	2K11	Final	FYS + 24 Months	2011/03/01	2013/02/28		24
10	2012	10H1	1St Estimate	FYS + 6 Months	2012/03/01	2012/08/31		6
11	2012	10H2	2nd Estimate	FYS + 12 Months	2012/09/01	2013/02/28		12
12	2012	2K10	ROE	FYS + 18 Months	2012/03/01	2013/08/31		18
13	2012	2K10	Final	FYS + 24 Months	2012/03/01	2014/02/28		24
Plan12

Cell Formulas
Range	Formula
E2:E13	E2	=EDATE(DATE(A2,3,1),IF(MOD(ROWS(E$2:E2),4)=2,6,0))
F2:F13	F2	=EDATE(DATE(A2,3,1),H2)-1
H2:H13	H2	=6*(MOD(ROWS(H$2:H2)-1,4)+1)

M.