Working out an average in days, months years

Hayley_A

New Member
Joined
Sep 19, 2019
Messages
7
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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)
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top