Calculate nuymber of month dinamicaly

[webdevptg]

Hi!

I'm having difficulty finding a formula that gives me the number of months a supervisor was responsible for the project, in a given month there can only be 1 supervisor.

Example: The first supervisor (Anh Bohan) started on October 15, 2020 and ended in February 2021, which will give 4 months, because 2nd supervisor (Eusebia Worde) started functions on February 1, 2021.

The last supervisor will have 3 months because it will start on October 15th 2021 and the project ends in December 2021.

I would like to dynamically calculate the number of months that a certain supervisor is in a role, is this possible?

Thanks!

Attach image example

 

[cmowla]

Hi!

You either have to give an exhaustive list of possible ways you type comments in the column to the right of where the numbers ought to be calculated. Also (with no disrespect), I could make it try to "make up" for typos like fev instead of feb (I thought this was for Portuguese for a second), but there is only so much I (or anyone) can predict as to what was meant.

You can see that the following formula works of the written out "date to date" is uniform with no typos (green cells) but it will give an error if that is not the case (yellow cells).

(Note: This code "doesn't mind" if there is more than one space before (or after) any of the words. But if you type "m ay" instead of "may", it won't be okay.)

Blank - Copy.xlsx
	A	B	C	D	E
1
2				4	oct 2020 to jan 2021
3				#VALUE!	fev 2021 to apr 2021
4				3	may 2021 to jul 2021
5				2	aug 2021 to sep 2021
6				#VALUE!	oct 2021 to finish (dec 2021)
7				4	oct 2020 to jan 2021
8				3	feb 2021 to apr 2021
9				3	may 2021 to jul 2021
10				2	aug 2021 to sep 2021
11				3	oct 2021 to dec 2021
Sheet1 (2)

Cell Formulas
Range	Formula
D2:D11	D2	=DATEDIF(MONTH(MID(TRIM(MID(E2,1,SEARCH("to",E2,1)-2)),1,3)&"1")&"/1/"&MID(TRIM(MID(E2,1,SEARCH("to",E2,1)-2)),SEARCH("2",TRIM(MID(E2,1,SEARCH("to",E2,1)-2)),1),LEN(TRIM(MID(E2,1,SEARCH("to",E2,1)-2)))),MONTH(MID(TRIM(MID(E2,SEARCH("to",E2,1)+2,LEN(E2))),1,3)&"1")&"/1/"&MID(TRIM(MID(E2,SEARCH("to",E2,1)+2,LEN(E2))),SEARCH("2",TRIM(MID(E2,SEARCH("to",E2,1)+2,LEN(E2))),1),LEN(TRIM(MID(E2,SEARCH("to",E2,1)+2,LEN(E2))))),"M")+1

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D
1		15/10/2020
2		29/12/2021
3
4
5	abc	X	15/10/2020	4
6			01/11/2020
7			01/11/2020
8			01/11/2020
9			10/12/2020
10			15/12/2020
11		X	01/02/2021	3
12			15/10/2020
13			15/10/2020
14			15/10/2020
15			15/10/2020
16			15/10/2020
17		X	17/05/2021	3
18			15/10/2020
19			16/10/2020
20		X	23/08/2021	2
21
22		X	15/10/2021	3
Master

Cell Formulas
Range	Formula
D5:D22	D5	=IF(B5="X",DATEDIF(C5,IFNA(EOMONTH(INDEX(C6:C102,MATCH("X",B6:B102,0)),-1),B$2),"m")+1,"")

 

[webdevptg]

How about

+Fluff 1.xlsm
	A	B	C	D
1		15/10/2020
2		29/12/2021
3
4
5	abc	X	15/10/2020	4
6			01/11/2020
7			01/11/2020
8			01/11/2020
9			10/12/2020
10			15/12/2020
11		X	01/02/2021	3
12			15/10/2020
13			15/10/2020
14			15/10/2020
15			15/10/2020
16			15/10/2020
17		X	17/05/2021	3
18			15/10/2020
19			16/10/2020
20		X	23/08/2021	2
21
22		X	15/10/2021	3
Master

Cell Formulas
Range	Formula
D5:D22	D5	=IF(B5="X",DATEDIF(C5,IFNA(EOMONTH(INDEX(C6:C102,MATCH("X",B6:B102,0)),-1),B$2),"m")+1,"")

Thats it, thank a lot

 

[Fluff]

You're welcome & thanks for the feedback.