PDA

View Full Version : 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
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"),"")

mdmackillop
07-23-2017, 09:55 AM
As Post #2

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