DatedIf Column in yymm format requiring to be averaged

[isassy16]

I have been trying to solve this for several days now. I have a column of 'time served in position' that was calculated via a DatedIf formula and I need it in the yymm output. 5y3m, 1y2m and so on. I then need the average of all the time served in position below but it keeps giving me an error code. Can I not do an average of datedif values in yymm format? Snippet of column attached. Thank you for any insight on this.

 

[GlennUK]

Just use month as a result .... then you can average that, and convert to ym afterwards.

 

[Alex Blakenburg]

I would also use a helper column showing the difference in months.
Your issue is that you are years / months are text and you can't average text.

If you really want to keep it the way it is you could try something like this:

20230714 Date ym average isassy16.xlsx
	J	K
1	Current Years in position	Average
2	1y9m	4y9m
3	3y10m
4	4y9m
5	2y10m
6	5y10m
7	4y6m
8	10y3m
Sheet1

Cell Formulas
Range	Formula
K2	K2	=LET(avgmths,SUM(TEXTBEFORE(J2:J8,"y")*12,--TEXTBEFORE(TEXTAFTER(J2:J8,"y"),"m"))/COUNTA(J2:J8), avgyrs, INT(avgmths/12), avgyrs & "y" & INT(avgmths-(avgyrs*12)) & "m")

 

[isassy16]

I would also use a helper column showing the difference in months.
Your issue is that you are years / months are text and you can't average text.

If you really want to keep it the way it is you could try something like this:

 

[isassy16]

Alex Blankenburg, this worked perfectly. So awesome. Thank you so very much!

 

[Alex Blakenburg]

You're welcome. Glad we could help.