+ Reply to Thread
Results 1 to 10 of 10

averaging formula with DATEDIF

  1. #1
    Registered User
    Join Date
    04-08-2021
    Location
    usa
    MS-Off Ver
    2016
    Posts
    4

    averaging formula with DATEDIF

    I've been looking all day and can't get this to work.

    I have 20 rows with start date (column A) and end date (column B) and using the DATEDIF (column C) formula calculate months and days. This was easy, no problem.

    What I would like to do is average the 20 rows and keep the answer in the DATEDIF format with months and days. Some of my rows have no end date (still in progress). How do I format the equation to skip these rows until an end date is entered?

    My current formula
    =DATEDIF(AVERAGE(A2:A21),AVERAGE(B2:B21),"m")&"m, "&DATEDIF(AVERAGE(A2:A21),AVERAGE(B2:B21),"md")&" d"
    does not skip these rows and brings my average down to an incorrect level. It currently gives an answer of 2 m, 22 d instead of 4 m, 12 d because the blank dates is bringing down the average.

    Any sage advise?

    Thanks
    Butch
    Last edited by butchcassidy23; 04-08-2021 at 01:35 PM.

  2. #2
    Registered User
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    2019
    Posts
    72

    Re: averaging formula with DATEDIF

    Hi butchcassidy23,

    I think all you have to do is change average to averageif (not sure since there is no excel file to test it with)

    With that:
    Please Login or Register  to view this content.
    Hope this is the answer you're looking for.

  3. #3
    Registered User
    Join Date
    04-08-2021
    Location
    usa
    MS-Off Ver
    2016
    Posts
    4

    Re: averaging formula with DATEDIF

    I'll try to attach. If I did it correctly, cell C22 shows my current attempt with a lower average because of the blank dates in column B.

    I also tried your suggestion in D2, but get an error if I put a space between the <>, or the same answer as C22 if I remove the space.

    What do you think?
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    2019
    Posts
    72

    Re: averaging formula with DATEDIF

    Hi butchcassidy23,

    There shouldn't be any spaces in between.

    "<>" means no blanks. With that I get the same value in C22.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    2019
    Posts
    72

    Re: averaging formula with DATEDIF

    Hi butchcassidy23,

    Sorry, I've finally critically read the thread and finally realize the issue. I've attached the excel because I'm not getting 4m 12d.

    Please see excel file:
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-08-2021
    Location
    usa
    MS-Off Ver
    2016
    Posts
    4

    Re: averaging formula with DATEDIF

    D13L,

    Yes sir, that's the one. I appreciate you coming back and looking at it again.

    Thank you
    Butch

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,873

    Re: averaging formula with DATEDIF

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  8. #8
    Registered User
    Join Date
    04-08-2021
    Location
    usa
    MS-Off Ver
    2016
    Posts
    4

    Re: averaging formula with DATEDIF

    AliGW,

    I will take care of both those things. Thank you for making me aware.

    Butch

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,806

    Re: averaging formula with DATEDIF

    Removed by FR ... duplicated post.
    Last edited by FlameRetired; 04-09-2021 at 03:13 PM. Reason: Duplicated post.
    Dave

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,806

    Re: averaging formula with DATEDIF

    I'm a little late but I managed to get 4 m 12 d with this.

    In Excel 2016 you may have to array enter this.
    If you arenít familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. DATEDIF Formula HELP
    By vforrestt in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-17-2019, 05:36 PM
  2. [SOLVED] DATEDIF - DATEDIF Calculation returning a negative for days or months
    By DaveBre in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-27-2015, 06:22 PM
  3. [SOLVED] Help with DateDif formula
    By FieldHaven in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-22-2014, 11:53 AM
  4. [SOLVED] Datedif Formula????
    By Bosox76 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-03-2013, 11:36 AM
  5. [SOLVED] DATEDIF formula
    By Nubian in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-01-2013, 10:09 AM
  6. DateDif Average? Damn DateDif
    By UTCHELP in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 12-10-2012, 08:53 AM
  7. Averaging out data from a DATEDIF Formula
    By DarrenH in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-14-2008, 07:27 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1