# Help with #days lapsed calculation

#### kimknguyen

##### Member
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

Thank you very much
kim

#### Attachments

• 501.3 KB Views: 5

#### AlanSidman

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

#### kimknguyen

##### Member
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

• 57.7 KB Views: 5

#### AlanSidman

##### Active Member
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

• 834.8 KB Views: 4

#### kimknguyen

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

#### AlanSidman

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

#### kimknguyen

##### Member
Thank you AlanSidman for your help.
kim

kimknguyen

#### p45cal

##### Well-Known Member
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

• 956.7 KB Views: 8

#### kimknguyen

##### Member

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

##### Well-Known Member
Look at the settings for Sum of Date:

#### kimknguyen

##### Member
Hi p45cal,
Thank you! Thank you!
kim