Working out an average in days, months years

[Hayley_A]

So i'm having a little difficulty working out how to get an average formula to work in regards to days, months & years.

I have a spreadsheet which will track employee absence and i would like to be able to see the average duration for absenteeism across all employees. See below for an example of data:

Col. E Col. K Col. L
2 F.D.O.A Return to Work Date Length of Absence
3 01/01/2020 02/01/2020 1 days
4 01/01/2020 03/01/2020 2 days
5 01/01/2020 04/01/2020 3 days
6 01/01/2020 05/01/2020 4 days
7 01/01/2020 06/01/2020 5 days


The length of absence is currently being calculated using:
=IF(DATEDIF([@[F.D.O.A]],[@[Return to Work Date]],"y")=0, "", DATEDIF([@[F.D.O.A]],[@[Return to Work Date]],"y") & " years ") & IF(DATEDIF([@[F.D.O.A]],[@[Return to Work Date]],"ym")=0,"", DATEDIF([@[F.D.O.A]],[@[Return to Work Date]],"ym") & " months ") & IF(DATEDIF([@[F.D.O.A]], [@[Return to Work Date]], "md")=0, "", DATEDIF([@[F.D.O.A]], [@[Return to Work Date]], "md") & " days")

I would like to use the length of absence data and get an average duration of absence. Preferably the average would also follow the days, months, years format. I will also need the formula to ignore any blank cells within the length of absence column.

In essence, working from the above example i would get an average of 3 days (1 day + 2 days + 3 days + etc. / 5 = 3).

I've tried different formulae to try and work this out but none seem to work

Any help would be greatly appreciated!

Ta
Hayley

 

[AlphaFrog]

Your Length Of Absence (LOA) formula returns a text result. If you want to use those results in further calculations and formulas, you'll need numeric results. Perhaps it would be easier to just have a helper column to calculate LOA in days only (just a numeric value). Then you can do an average on the numeric LOA column and display that result in your prefered format. You could also use the numeric LOA column to have a shortened formula in your formatted LOA text column (L)

 

[Hayley_A]

Great thank you!