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

#### Lyryx

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

HTML:
``=IF(COUNTIF([Unique ID],[@[Unique ID]])>1,"Check For Transfer","")``
and
HTML:
``=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.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### offthelip

##### Well-known Member
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.
Code:
``=10*ROUNDDOWN(NOW()/10,0)``

Replies
0
Views
96
Replies
0
Views
433
Replies
3
Views
56
Replies
1
Views
191
Replies
8
Views
557