Comparing Dates

**tomkat743** · 07-23-2017, 06:13 AM

I have code that deletes orders that were paid after 90 days.

Sub myMacro()
     paidColumn = "B"
     dueColumn = "D"
     firstRow = 2
     lastRow = Range(paidColumn & Rows.Count).End(xlUp).Row
     r = firstRow

     Do Until r > lastRow
          paidValue = Range(paidColumn & r).Value
          dueValue = Range(dueColumn & r).Value
          If paidValue > dueValue + 90 Then
               Rows(r).Delete
               r = r - 1
          End If
          r = r + 1
     Loop
End Sub

I need a much more complicated version of this that does a match and lookup on two separate sheets and returns the words over 90 or under 90 so I can know if the invoices were paid within 90 days. I don't know if I am not posting the question with enough information or if what I am asking is to complicated. I have posted to Mr. Excel twice and received no responses at all

- I am more than willing to pay someone for some help on this and sent in messages and emails to their pay for play addresses and still no response - I've been working on this for months and maybe it is just not possible but somehow in my mind I believe it is possible I just need to get to the right person.

I have copied the sheets below with column G on the payments sheet being the column to populate. I need to match column "E" Invoice number on the payment sheet to column "B" on the All Inv Data sheet then look at the date in column "D" of the payment sheet and compare it to the Date in column "D" in the All Inv Data sheet and see if it is over or under the 90 day window.

As you can see in the below example the result should be over90 or under90 returned to column G on the payment sheet. As you can see that some of the invoice numbers have text before the numbers like QA1024 I don't need to do anything with these as these are credit memos or something other than paid invoices so I can skip or return a 0 or whatever.

One issue may be that the below data set contains random payments and random lines from all inv data whereas my actual sheet of payments contains about 500 invoices and my all inv data sheet has about 10,000 invoices so the below data set may have no matching invoice numbers (could this be what is making it complicated to help me) If so is there another way for me to upload both full sheets?

Is the failure on me that I am not explaining the process well enough or not supplying enough data or is this just very complicated? Or is there some other reason I may not be receiving any help on this issue? Please Please Please someone give me a direction or some feedback as to my issue. Thank you in advance for any support you can give me on this.

SHEET 1
PAYMENTS
Customer	Sales Rep	Type	Date	Invoice#	amount	over/under
NE-UNP	SR	91491	1/18/2017	129730	$10,676.53	over 90
NE-UNP	SR	91491	1/18/2017	129731	$13,816.69	over 90
NE-UNP	SR	91491	1/18/2017	129732	$199.99	over 90
NE-UNP	SR	91491	1/18/2017	135939	$191.84	under 90
NE-UNP	SR	FIX INV	3/14/2017	129731	-$7,170.57	over 90
NE-UNP	SR	FIX INV	3/14/2017	138612	-$6,646.12	under 90
NE-UNP	SR	FIX INV	3/14/2017	OA1024	$13,816.69	0.00
NE-UNP	SR	93563	4/10/2017	129729	$46,701.17	over 90
NE-UNP	SR	93563	4/10/2017	129730	$6,048.24	over 90
NE-UNP	SR	93563	4/10/2017	129733	$10,953.76	over 90
NE-UNP	SR	93563	4/10/2017	OA1057	$50.59	0.00
NE-UNP	SR	APPLY CM	5/12/2017	129732	$22,289.38	over 90
NE-UNP	SR	APPLY CM	5/12/2017	129938	$13,025.94	over 90
NE-UNP	SR	APPLY CM	5/12/2017	129939	$858.35	over 90
NE-UNP	SR	APPLY CM	5/12/2017	130141	$241.39	over 90
NE-UNP	SR	APPLY CM	5/12/2017	130289	$155.39	over 90
NE-UNP	SR	APPLY CM	5/12/2017	130290	$18,716.70	over 90
NE-UNP	SR	APPLY CM	5/12/2017	135769	$913.50	over 90
NE-UNP	SR	APPLY CM	5/12/2017	139798	-$645.32	under 90
NE-UNP	SR	APPLY CM	5/12/2017	139804	-$41,688.05	under 90
NE-UNP	SR	APPLY CM	5/12/2017	OA1024	-$13,816.69	0.00
NE-UNP	SR	APPLY CM	5/12/2017	OA1057	-$50.59	0.00

SHEET 2
All INV DATA
CUSTOMER #	INVOICE	DOCUMENT	DATE
NE-UNP	129729	122408	12/17/2015
NE-UNP	129730	122414	12/17/2015
NE-UNP	129731	122415	12/17/2015
NE-UNP	129732	122416	12/17/2015
NE-UNP	129733	122413	12/17/2015
NE-UNP	129938	122408	12/28/2015
NE-UNP	129939	122414	12/28/2015
NE-UNP	129981	122408	12/29/2015
NE-UNP	130140	122418	12/31/2015
NE-UNP	130141	122415	12/31/2015
NE-UNP	130288	122408	1/12/2016
NE-UNP	130289	122415	1/12/2016
NE-UNP	130290	122416	1/12/2016
NE-UNP	130389	122414	1/15/2016
NE-UNP	130782	122408	2/9/2016
NE-UNP	131517	122414	3/23/2016
NE-UNP	131518	122408	3/23/2016
NE-UNP	132279	122415	5/2/2016
NE-UNP	135767	122414	10/25/2016
NE-UNP	135768	122415	10/25/2016
NE-UNP	135769	122416	10/25/2016
NE-UNP	135939	125912	11/2/2016
NE-UNP	136134	125912	11/15/2016
NE-UNP	136590	C02299	12/2/2016
NE-UNP	136590	C02299	12/2/2016
NE-UNP	138165	125912	2/16/2017
NE-UNP	138612	C02517	3/14/2017
NE-UNP	138613	D01897	3/14/2017
NE-UNP	139190	128254	4/6/2017
NE-UNP	139735	128494	5/5/2017
NE-UNP	139798	C02652	5/9/2017
NE-UNP	139804	C02655	5/9/2017
NE-UNP	140194	127056	5/26/2017

**onlyadrafter** · 07-23-2017, 06:56 AM

Hello,

try this in G2 on Payments sheet

=IF(ISERROR(INDEX('ALL INV DATA'!D: D,MATCH(PAYMENTS!E2,'ALL INV DATA'!B:B,0))),0,IF(D2-INDEX('ALL INV DATA'!D: D,MATCH(PAYMENTS!E2,'ALL INV DATA'!B:B,0))<90,"under 90","over 90"))

and copy down. (You need to remove the gaps within D: D). This gives the sames results as your example.

Not sure how slow this will be with all your data.

Does this solve all your questions?

**SamT** · 07-23-2017, 07:08 AM

Look up invoice numbers
Compare Dates
Skip InvNums that don't start with a numeral

That's it?

What about negative Payment Amounts?

Important Questions:
Can the sheets be sorted by InvNum?

Are all Date Columns Formatted as a form of Date? (Not as Text that looks like a date)(You can test by formatting any date cell as a number. if the result is a number is in the area of 42,000 plus or minus 1000, it's a Date, not Text)

**snb** · 07-23-2017, 07:53 AM

Not posting a sample file is always 'too complicated'.

**mdmackillop** · 07-23-2017, 08:12 AM

... and a meaningful title descriptive of the issue. see FAQ

**tomkat743** · 07-23-2017, 09:14 AM

I added a sample file to my post I had to truncate it to fit the sites 1mb file size requirement but still has about 15k rows so should work for most part.

**tomkat743** · 07-23-2017, 09:15 AM

This only gives me a 0 in each cell not sure if issue is with subtracting dates as dates where maybe I could reformat dates as numbers?

**tomkat743** · 07-23-2017, 09:16 AM

I added a sample file. Thanks for looking

**tomkat743** · 07-23-2017, 09:21 AM

All date columns are formatted mm/dd/yyyy.
Sheets can not be sorted.
payment amounts are not considered, I am only looking for payments processed greater that 90 days out or less than 90 days out whether the payment is a credit or debit makes no difference.

**p45cal** · 07-23-2017, 09:51 AM

Originally Posted by onlyadrafter

Hello,

try this in G2 on Payments sheet

=IF(ISERROR(INDEX('ALL INV DATA'!D: D,MATCH(PAYMENTS!E2,'ALL INV DATA'!B:B,0))),0,IF(D2-INDEX('ALL INV DATA'!D: D,MATCH(PAYMENTS!E2,'ALL INV DATA'!B:B,0))<90,"under 90","over 90"))

and copy down. (You need to remove the gaps within D: D). This gives the sames results as your example.

Not sure how slow this will be with all your data.

Does this solve all your questions?

An excellent answer!
In Excel 2007 and above we can make use of IfError in onlyadrafter's formula:
=IFERROR(IF(D2-INDEX('All INV DATA'!D:D,MATCH(E2,'All INV DATA'!B:B,0))<90,"under 90","over 90"),"")

also shorter but probably less efficient:
=IFERROR(IF(D2-VLOOKUP(E2,'All INV DATA'!B:D,3,0)<90,"under 90","over 90"),"")

**tomkat743** · 07-23-2017, 07:06 PM

Thank you all so much the VLOOKUP worked fine. I could not get the Index, Match to work.

**SamT** · 07-23-2017, 08:18 PM

THis goes in the payments sheet code module. note that it will take a while to run the first time. After that always sort Payments so the last (unchecked) payment dates are at the bottom

Option Explicit

Sub CheckPaymentDates()
Dim Found As Range
Dim InvDate As Range
Dim Cel As Range
Dim WorkRng As Range

Set WorkRng = Range(Cells(Rows.Count, "G").End(xlUp), Cells(Rows.Count, "F").End(xlUp).Offset(, 1))

Application.ScreenUpdating = False
For Each Cel In WorkRng
  If Not IsNumeric(Cel.Offset(, -2)) Then GoTo NextCell
  
  Set Found = Sheets("ALL INV DATA").Range("B:B").Find(Cel.Offset(, -2))
  If Found Is Nothing Then 
Cel = "Invoice Not Found"
Cel.Interior.ColorIndex = 3
GoTo NextCell 
End If
  
  Set InvDate = Found.Offset(, 2)
  
  If Cel.Offset(, -3) - InvDate > 90 Then
    Cel = "Over 90"
  Else
    Cel = "Under 90"
  End If
NextCell:
Next

Application.ScreenUpdating = True
End Sub

I had a lot of unfound invoices. I assume they were truncated out when you uploaded the sample

Thread: Comparing Dates

Thread Tools

Display

Comparing Dates

Posting Permissions