Formula Matching a +/- 5 days of Date

[smoskowitz]

Hello --

I have some data like the line below. What I have done is JOINED on the GlobalTaxNumber and CompanyGlobalTaxNumber. The I  wrote a formula that tells me if the Dividend Paid Converted - Dividend Received Converted is less then the 5% of Dividend Column. Based on this alone I will get a MATCH or NO MATCH. The problem is that due to the volume of data there is a lot of duplicates that match (false positives).

The one piece that will help are the dates. Usually, there would be a match if the date and right_date are identical -- but that's not the way the business works. However, we can be confident that if the dates are within 5 days of each other -- we have a match.

I cannot figure out how to write a formula that would give me a match on the example below. As we can see 9/13/2017 is within 9/11/2017 -- so this should be a match. 

Any ideas would be greatly appreciated.

GlobalTaxNumber	CompanyGlobalTaxNumber	Right_GlobalTaxNumber	Right_CompanyGlobalTaxNumber	Date	Right_Date	Dividend Paid Converted	Dividend Received Converted	Right_Transaction Month	5% of Dividend Paid	Difference
RO992	NL881	NL881	RO992	9/11/2017	9/13/2017	194710.7	193726.7	9/13/2017	9735.534	NO MATCH

Thanks,

Seth

[danilang · Accepted solution]

Hi @smoskowitz 

The following formula gives you what you're looking for

abs(DateTimeDiff([date1],[date2],"days"))<=5

However, the definition of "Within 5 days" is somewhat ambiguous, so you may have to change "<=5" to fit your requirements

Dan

[smoskowitz]

Brilliant! Thank you!