Count Date Sequence Based on "Step" Condition in Another Cell

[Pepperoni]

Hi everyone,

I have tried to look around to answer this myself, but I don't even know if I'm explaining it right to find the information.

I am attempting to make a template that will auto calculate a sequence of dates based on conditions in another cell. For example, if a user was to select 'Weekly' Payslips in B6, the start and end dates in B and C 14, should automatically calculate weekly dates for a period of time from the date entered in B11. However, if they were to select 'Fortnightly' payslips, it would auto change to a fortnightly date range for a number of weeks from the start date in B11 (I have attached a screen shot)

The closest I have found so far is to nest EDATE and Sequence...but from there it just goes silly - as you can see! I need this to auto calculate so there is no potential of an incorrect date range being manually entered.
Any help is much appreciated!

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book2
	A	B	C	D
1
2
3
4
5
6	Payslip Periods	Weekly
7
8	Subsidy Start Date	1/1/2022
9	Subsidy End Date	2/7/2022
10	Payable
11	Payslip Start Date	1/1/2022
12
13		Payslip Start	Payslip End	Payslip Hours
14		1/1/2022	1/7/2022
15		1/8/2022	1/14/2022
16		1/15/2022	1/21/2022
17		1/22/2022	1/28/2022
18		1/29/2022	2/4/2022
19		2/5/2022	2/11/2022
20		2/12/2022	2/18/2022
Sheet7

Cell Formulas
Range	Formula
B14:B39	B14	=SEQUENCE(26,,B11,IF(B6="Weekly",7,14))
C14:C39	C14	=B14#+IF(B6="Weekly",6,13)
Dynamic array formulas.

 

[Pepperoni]

Welcome to the MrExcel forum!

Try:

Book2
	A	B	C	D
1
2
3
4
5
6	Payslip Periods	Weekly
7
8	Subsidy Start Date	1/1/2022
9	Subsidy End Date	2/7/2022
10	Payable
11	Payslip Start Date	1/1/2022
12
13		Payslip Start	Payslip End	Payslip Hours
14		1/1/2022	1/7/2022
15		1/8/2022	1/14/2022
16		1/15/2022	1/21/2022
17		1/22/2022	1/28/2022
18		1/29/2022	2/4/2022
19		2/5/2022	2/11/2022
20		2/12/2022	2/18/2022
Sheet7

Cell Formulas
Range	Formula
B14:B39	B14	=SEQUENCE(26,,B11,IF(B6="Weekly",7,14))
C14:C39	C14	=B14#+IF(B6="Weekly",6,13)
Dynamic array formulas.

This is actually perfect @Eric W Thank you so much.

One last question, could I combine the sequence function in Column B to do the same calculation for a specific number of rows for each payslip period range.
Example:

User Selects 'Fortnightly' and the sequence range displays 13 rows of sequential dates. User selects 'Monthly' and it will display 6 rows of sequential months.

Thank you!

 

[Peter_SSs]

If "Monthly" does that actually mean a calendar month running from the 1st of the month to the last day of the month or does it mean a 4-week period, or something else?

 

[Pepperoni]

If "Monthly" does that actually mean a calendar month running from the 1st of the month to the last day of the month or does it mean a 4-week period, or something else?

Hi @Peter_SSs,
Monthly would be every 4-weeks.

Thank you.

 

[Peter_SSs]

Try this adaptation then.

22 01 21.xlsm
	A	B	C
1
2
3
4
5
6	Payslip Periods	Monthly
7
8	Subsidy Start Date	1/01/2022
9	Subsidy End Date	7/02/2022
10	Payable
11	Payslip Start Date	1/01/2022
12
13		Payslip Start	Payslip End
14		1/01/2022	28/01/2022
15		29/01/2022	25/02/2022
16		26/02/2022	25/03/2022
17		26/03/2022	22/04/2022
18		23/04/2022	20/05/2022
19		21/05/2022	17/06/2022
20
Pays

Cell Formulas
Range	Formula
B14:B19	B14	=LET(p,MATCH(B6,{"Weekly","Fortnightly","Monthly"},0),SEQUENCE(CHOOSE(p,26,13,6),,B11,CHOOSE(p,7,14,28)))
C14:C19	C14	=B14#+IF(B6="Weekly",6,IF(B6="Monthly",27,13))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B6	List	Weekly,Fortnightly,Monthly

 

[Pepperoni]

Try this adaptation then.

22 01 21.xlsm
	A	B	C
1
2
3
4
5
6	Payslip Periods	Monthly
7
8	Subsidy Start Date	1/01/2022
9	Subsidy End Date	7/02/2022
10	Payable
11	Payslip Start Date	1/01/2022
12
13		Payslip Start	Payslip End
14		1/01/2022	28/01/2022
15		29/01/2022	25/02/2022
16		26/02/2022	25/03/2022
17		26/03/2022	22/04/2022
18		23/04/2022	20/05/2022
19		21/05/2022	17/06/2022
20
Pays

Cell Formulas
Range	Formula
B14:B19	B14	=LET(p,MATCH(B6,{"Weekly","Fortnightly","Monthly"},0),SEQUENCE(CHOOSE(p,26,13,6),,B11,CHOOSE(p,7,14,28)))
C14:C19	C14	=B14#+IF(B6="Weekly",6,IF(B6="Monthly",27,13))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B6	List	Weekly,Fortnightly,Monthly

Amazing! Thank you so much! I would have never found this as I was probably asking all the wrong questions.
Thank you again! Off to practice!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

If interested, here is a slightly shorter alternative for B14. ?

22 01 21.xlsm
	A	B	C
1
2
3
4
5
6	Payslip Periods	Fortnightly
7
8	Subsidy Start Date	1/01/2022
9	Subsidy End Date	7/02/2022
10	Payable
11	Payslip Start Date	1/01/2022
12
13		Payslip Start	Payslip End
14		1/01/2022	14/01/2022
15		15/01/2022	28/01/2022
16		29/01/2022	11/02/2022
17		12/02/2022	25/02/2022
18		26/02/2022	11/03/2022
19		12/03/2022	25/03/2022
20		26/03/2022	8/04/2022
21		9/04/2022	22/04/2022
22		23/04/2022	6/05/2022
23		7/05/2022	20/05/2022
24		21/05/2022	3/06/2022
25		4/06/2022	17/06/2022
26		18/06/2022	1/07/2022
27
Pays

Cell Formulas
Range	Formula
B14:B26	B14	=LET(p,FIND(LEFT(B6,1),"WFM"),SEQUENCE(CHOOSE(p,26,13,6),,B11,CHOOSE(p,7,14,28)))
C14:C26	C14	=B46#+IF(B6="Weekly",6,IF(B6="Monthly",27,13))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B6	List	Weekly,Fortnightly,Monthly