SOLVED

How to calculate days elapsed between transaction

New Contributor

Hello, 

 

I work for a non-profit and am trying to get insight into how frequently each of our donors contribute financially. I have a donor register with names, dates, amounts. I currently am summarizing this information on a pivot table.

 

However, is there a way to find the average days elapsed between each giving transaction? It would need to be specific per donor. 

 

It would be helpful to analyze how each of our donors individually gives, in order to help our future campaigns. 

6 Replies
Can you please post few sample data (better to attach a sample excel file) and show your desired output.

@Harun24HR thanks for your response. 

 

I want to find the days between each transaction, per PayerID, for repeat donors.

This exercise seeks to find the frequency and ultimately average time between each transaction, per repeat donor.

PayerIDAmountDate Received
145866294651.111/4/2022
1216727046720011/4/2022
1213176351911011/3/2022
121317576422500011/3/2022
7809555286216/26/2022
7809555286386/22/2022
7804539738102.210/17/2022
773625538161.326/17/2022
73965119851006/14/2022
739239907010006/12/2022
7392361055400009/4/2022
738844573120.446/24/2022
7368787451259/13/2022
7368787451258/13/2022
736878745125.558/13/2022
7368787451257/13/2022
736878745125.557/13/2022
7368787451102.26/7/2022
7354195806106/4/2022
73541843011006/4/2022
best response confirmed by Grahmfs13 (Microsoft)
Solution

@Jakenash 

If you're on 365, this might be your solution:

 

=LET(filtered,FILTER($F$2:$F$21,$D$2:$D$21=I2),dates,COUNT(filtered),IF(dates>1,AVERAGE(DROP(filtered,-1)-DROP(filtered,1)),filtered))
Wow, this is exactly what I was looking for. You're a wizard!

Do you mind explaining the formula? I can follow that you took the original list with PayerIDs, amounts, and dates and then create a unique column of PayerIDs from which your =LET formula calculated from.

What does "filtered", and "dates" and "DROP" mean? Thanks in advance.

@Jakenash 

LET allows you to assign calculation results to names. 

 

"Filtered" is the name used to refer to the below.  FILTER obtains the date(s) for a given ID.

FILTER($F$2:$F$21,$D$2:$D$21=I2)

"Dates" refers to the below. We're counting how many dates a given ID returns.

COUNT(filtered)

 

The logic.  If the number of dates is more than 1, drop 1 date at the end and subtract from it - all but the first date and then find the average of the differences found.  If there's one date, show the date.

IF(dates>1,AVERAGE(DROP(filtered,-1)-DROP(filtered,1)),filtered)

 

Essentially, this is the subtraction the above is doing:

Patrick2788_0-1667928030375.png

The subtraction is done by making 2 staggered lists of dates so when the subtraction is done it will find the difference between dates. Then we average the difference.

 

 

lol wow, this is awesome. thanks again. very sharp!