Which formula to use

[santnok]

Hi
Hope somone can help me out with a formula here.
I have have a start date and a end date column for each project.
As you see, in the column for the month Jan, Feb and so on I have manually filled out how many days from the start date to the end date.
So for project 1 I have to fill out 16 days for Jan and 28 days for feb and 31 days for Mar and 18 days for Apr.
And for the Project 2 I must do the same thing.

Is there a way to use a formula that can fill out the numbers automaticlly when and give a start and end date?

Bok1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1
2							Start date			01.01.2021
3							End date			31.12.2022
4
5
6
7							2021												2022
8	Projectname	Project number	Start date	End date	Project days		Jan	Feb	Mar	Apr	Mai	Jun	Jul	Aug	Sep	Okt	Nov	Des	Jan	Feb	Mar	Apr	Mai	Jun	Jul	Aug	Sep	Okt	Nov	Des
9	Projekt 1	D3878	15.01.2021	18.04.2021	93		16	28	31	18
10	Projekt 2	D3879	13.05.2021	18.03.2022	309		18	28	31	30	31	30	31	31	30	31	30	31	31	28	18
Ark1

Cell Formulas
Range	Formula
E9:E10	E9	=SUM(D9-C9)

Thanks for all answers

 

[Eric W]

Try:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1
2							Start date			1/1/2021
3							End date			12/31/2022
4
5
6
7							2021												2022
8	Projectname	Project number	Start date	End date	Project days		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
9	Projekt 1	D3878	1/15/2021	4/18/2021	93		17	28	31	18	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	Projekt 2	D3879	5/13/2021	3/18/2022	309		0	0	0	0	19	30	31	31	30	31	30	31	31	28	18	0	0	0	0	0	0	0	0	0
Sheet4

Cell Formulas
Range	Formula
G9:AD10	G9	=MAX(0,MIN(EOMONTH(G$8,0),$D9)-MAX(G$8,$C9)+1)
E9:E10	E9	=D9-C9

Note that the G8:AD8 cells have an actual date in them, the 1st of the month for that month and year, and they're just formatted to show the month.

 

[Dave Patton]

Try the follwing

T202110b.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1
2							Start date			1-Jan-21
3							End date			31-Dec-22
4
5
6
7							2021												2022
8	Projectname	Project number	Start date	End date	Project days		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	Nov-22	Dec-22
9	Projekt 1	D3878	15-Jan-21	18-Apr-21	93		16	28	31	18	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	Projekt 2	D3879	13-May-21	18-Mar-22	309		0	0	0	0	18	30	31	31	30	31	30	31	31	28	18	0	0	0	0	0	0	0	0	0
2a

Cell Formulas
Range	Formula
G9:AD10	G9	=MAX(0,MIN(EOMONTH(G$8,0),$D9)-MAX(G$8-1,$C9))
E9:E10	E9	=D9-C9

 

[santnok]

Thank you verry much for all answers worked perfect
Can I ask also if its posible to automaticly set the row for Jan, Feb, Mar to be filled out for the range start and end date for each year?
I thinking then about the start and end date that are in the top og the sheet

Thanks again for help

 

[Eric W]

If you have Excel 365, clear out G8:AD8 and put this in G8:

=EDATE(J2,SEQUENCE(,24,0))

where 24 is the number of months you want.


If you don't have Excel 365, clear out G8:AD8 and put this in G8:

=EDATE($J$2,COLUMNS($G6:G6)-1)

and drag to the right as far as needed.


Also, we can adapt our formulas to show the number of work days if you like, instead of calendar days.

 

[santnok]

Thank you Eric W for that formula, That worked perfect