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.
# days in range of dates that includes some duplicates separated by two blank rows



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 ???

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

Sorry but my excel sheet that I posted was the wrong one. Here is the correct spreadsheet.

How is it different to the first one?
I'm not sure I completely understand what you want.


The previous one had too many tabs and this one only has the correct spreadsheet. I created a video explanation. https://www.screencast.com/t/OlF4YmjJD6ic


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 ...???
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!