Formula to Find matching Absolute Values in a Range of Dates?

[Lyryx]

Hi Everyone,

I've been developing a project for work that will analyse bank accounts to determine the value of a project. The problem is some of our projects have inter-account transactions. In order to determine the proper worth of the project we have to find any transfers between the accounts and remove them. This is a tedious process of us going through the data and looking for values in from account A that match the values leaving account B or vice versa.


To solve this I created two temporary Formula... it's been a bit of a band-aid solution however because it only identifies transfers that might have occurred on the exact same transaction date when sometimes a transfer might originate in Account A on a Friday and then after 3 business Days come out of of Account B. So this formula ends up ignoring transfers that occurred over the course of a week in business days.

So I was hoping to get a similar formula but have it look for values within a week of each other ... I'm not sure if that is even possible though which is why I am posting this message here.

The formulas are as follows:

=IF(COUNTIF([Unique ID],[@[Unique ID]])>1,"Check For Transfer","")

and

=CONCATENATE([@[Transaction Date]],"-",ABS([@[Withdrawal/Debits]]-[@[Deposits/Credits]]))

Where:

• The first formula Counts all instances of Unique IDs, if any return more than once than it COULD be a transfer... however sometimes it's a cancelled check out of the same account...
• [Unique ID] creates a text value combining the date and absolute value of the ranges of data.

I realize this might be more complicated or even impossible but if a solution exists I know it will be found here.


Once again the goal is to Identify transfers between two or more accounts, a transfer occurs when for example $100 leaves Account A and then enters Account B on the same day or within 5 business days.
The formula I am currently using only looks for the same day so I was wondering if it would be possible to get it to look for matches within 5 days or a week.

Thanks in advance!

 

[offthelip]

You could divide the date by 10 and round it down and then multiply it back up again. This will make all the dates in a 10 day period identical: e.g.

=10*ROUNDDOWN(NOW()/10,0)