# days in range of dates that includes some duplicates separated by two blank rows

  • I have a list of dates of customer purchase activity. I need a formula in column (J) that finds all dates that belong to a particular customer and figure out how many days since their previous order. The customers are separated by two blank rows. Each customer may have duplicate dates but I need to calculate since the last different date. Then I need to calculate the average from the range of those answers, (see the gray in the green columns). I don't know how to calculate if there is a different date, (from blank to blank). I manually did the first and last to show what I am trying to create. See attachment.

  • Hello,


    Thanks for having attached your file ...:wink:


    A few of questions to clarify :


    1. Have you noticed your Column A does contain sometimes Text ... and sometimes actual Dates ...?


    2. Are in your dates in Column A always sorted in a descending order ?


    3. When you say you need a Formula ... do you really mean an Excel formula ... or would a macro be OK ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • I don't see that the dates are sometimes text and sometimes actual date format.
    The dates are always sorted in descending order.
    I would prefer an Excel formula because I understand formulas better but a macro would be ok.


    Thanks

  • How is it different to the first one?


    I'm not sure I completely understand what you want.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Hi,


    In cell K6 ... just add tis formula


    Code
    =ISNUMBER(A6)


    If you are getting FALSE .. it means that cell A6 contains Text AND NOT a Date ...


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thanks, Carim, all the dates in column A seem to be FALSE or Text but I don't understand how that helps me with the original question.

  • Thanks, Carim, all the dates in column A seem to be FALSE or Text but I don't understand how that helps me with the original question.


    When you are using Text ... you cannot perform calculations ...


    Making sure your Dates are Actual dates ... (which means numbers) ... allows you to manipulate them as needed ...


    By the way ... you said your Column A is always properly stored in descending order ... how come cell A6 is before cell A9 ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!