A row of dates split into either weeks or months (depending on which was selected) based off of a start and end date

[atayaz]

Hello,

Hope someone can help me out, please.

I'm building a financial model template which needs to be adaptable to a range of project lengths. The number of columns need to be dependent on the length of the project and whether it is split into weeks or months. E.g. a 1 year long project split into months would have 12 columns.

Here is what it would it would look like split into months:

Project Title:

Manager:

Start Date:

01/01/2021​

Projected End Date:

01/02/2022​

Split into:

Months

Project Item

Jan-21​

Feb-21​

Mar-21​

Apr-21​

May-21​

Jun-21​

Jul-21​

Aug-21​

Sep-21​

Oct-21​

Nov-21​

Dec-21​

Jan-22​

Feb-22​

Mar-22​

Apr-22​

May-22​

Jun-22​

Jul-22​

Aug-22​

Sep-22​

Oct-22​

blah

blah

blah

blah

and in weeks:

Project Title:
Manager:
Start Date:	24/05/2021​
Projected End Date:	19/07/2021​
Split into:	Weeks
Project Item	24/05/2021​	31/05/2021​	07/06/2021​	14/06/2021​	21/06/2021​	28/06/2021​	05/07/2021​	12/07/2021​	19/07/2021​
blah
blah
blah
blah

Will clarify anything straight away if not clear.

Thank you!

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Project Title:
2	Manager:
3	Start Date:	01/01/2021
4	Projected End Date:	01/02/2022
5	Split into:	Months
6
7
8	Project Item	01/01/2021	01/02/2021	01/03/2021	01/04/2021	01/05/2021	01/06/2021	01/07/2021	01/08/2021	01/09/2021	01/10/2021	01/11/2021	01/12/2021	01/01/2022	01/02/2022
9	blah
10	blah
11	blah
12	blah
13
Main

Cell Formulas
Range	Formula
B8:O8	B8	=IF(B5="Months",EDATE(B3,SEQUENCE(,DATEDIF(B3,B4,"m")+1,0)),SEQUENCE(,1+(B4-B3)/7,B3,7))
Dynamic array formulas.

 

[atayaz]

Thank you for the welcome and fast reply.

I've just tried the formula, but do not have access to sequence. I think this is most likely to do with my IT departments control as I am on office 365 which should come with sequence from what I've read online.

Is there a way to do this without the use of sequence?

Thanks again.

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Project Title:
2	Manager:
3	Start Date:	01/01/2021
4	Projected End Date:	01/02/2022
5	Split into:	Months
6
7
8	Project Item	01/01/2021	01/02/2021	01/03/2021	01/04/2021	01/05/2021	01/06/2021	01/07/2021	01/08/2021	01/09/2021	01/10/2021	01/11/2021	01/12/2021	01/01/2022	01/02/2022	01/03/2022
9	blah
10	blah
11	blah
12	blah
Main

Cell Formulas
Range	Formula
B8:P8	B8	=IF($B$5="Months",EDATE($B$3,COLUMNS($B8:B8)-1),$B$3+COLUMNS($B8:B8)*7-7)