Results 1 to 3 of 3

Thread: Working out an average in days, months years

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Working out an average in days, months years

    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

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,102
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Working out an average in days, months years

    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)
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Working out an average in days, months years

    Great thank you!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •