Help with #days lapsed calculation

[kimknguyen]

Hi everyone:

Have: a list of customers sign-in with names and dates.
Want: to calculate the number of days elapsed since the last visit.
For example, Abby Spung has 4 visits: the resulting #DaysLapsed in as shown. See attached file
Please help with the formula.

Thank you very much
kim

 

[AlanSidman]

In D5 =DATEDIF([@Date],TODAY(),"d") and copy down

 

[kimknguyen]

Hi AlanSidman,
Thank you for your help. Would you please modify the equation to reflect the # date lapsed since the last visit (and NOT from today()).
Thanks,
kim

 

[AlanSidman]

So what you want is not days lapsed since last visit but days lapsed between visits. First, I sorted your column of names alphabetically, then in column D, I placed this formula and copied down =IFERROR(ABS(IF([@Name]=B8,[@Date]-C8,"")),""). I reformatted column D from Custom to number. See attached.

 

[kimknguyen]

Hi AlanSidman,
Is there a way we can do it without sorting the name of the columns alphabetically?
Thanks,
kim

 

[AlanSidman]

i don't have an alternative. We can only hope someone else knows of a way.

 

[kimknguyen]

Thank you AlanSidman for your help.
kim

 

[AlanSidman]

crosspost: https://www.excelforum.com/excel-fo...number-of-days-between-a-customer-visits.html

 

[vletm]

kimknguyen
Please, reread

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

 

[p45cal]

Alternatively… you can use a pivot table; in the attached the results where you wanted them using a GETPIVOTDATA formula which refers to the pivot table on Sheet1.
Of course, you may prefer to use the pivot table directly instead (it's much faster).

 

[kimknguyen]

Calculate number of days between a customer visits [SOLVED]

Hello, please help! Have: a list of customers sign-in dates. Want: to calculate the number of days between visits. For example: customer Abby Spung [please use the filter in "Name" to see. Please help with a formula that does not involve manual filtering. Please see attached file

www.excelforum.com

Hi P45cal,
Thanks for your help with my excel question on calculating the number of days between visit. AliGW of Excelforum solved the problem using a formula. The solution you suggested is using pivot table is very effective too. However, I tried it but did not get the results that in your pivot table. I think it has to do with the cell value setting. Can you take a few more minutes to see where I got it wrong? The image is attached since the file is too big to be attached.
Thank you very much,
kim

 

[p45cal]

Look at the settings for Sum of Date:

 

[kimknguyen]

Hi p45cal,
Thank you! Thank you!
kim