• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with #days lapsed calculation

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
 

Attachments

  • ToChandoo_#DateLapsed.xlsx
    501.3 KB · Views: 5
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
 

Attachments

  • ToCHandoo_#daylapsed.JPG
    ToCHandoo_#daylapsed.JPG
    57.7 KB · Views: 5
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.
 

Attachments

  • ToChandoo_#DateLapsed.xlsx
    834.8 KB · Views: 4
kimknguyen
Please, reread
 
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).
 

Attachments

  • Chandoo41598ToChandoo_#DateLapsed.xlsb
    956.7 KB · Views: 8

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
59995
 
Back
Top