find the next due date after today within a group in an Excel PivotTable

Question

I have got a table set up in one sheet with "transactions". Each row contains a name of a vendor, the amount owed or paid depending on transaction type, and the due date/transaction date. Here is some simplified sample data:

Vendor      Date    Invoice  Payment
Vendor A    6/30    $200       
Vendor A    6/30            ($200)  
Vendor B    7/5     $500    
Vendor B    7/5             ($500)
Vendor C    10/28   $50
Vendor A    10/30   $100
Vendor C    11/15   $50       

I have already built a PivotTable from that table to group these transactions by vendor and sum the remainder owed. What I'm trying to figure out is how to, for each vendor, get the next due date (min date of the group, excluding dates < Today()), or if there is no next due date then I want to see the max date for that group.

Here is what my PivotTable looks like, plus the date column I'd like to add (assuming Today() = 10/23):

Vendor      Date    Owed
Vendor B    7/5     -    
Vendor C    10/28   $100
Vendor A    10/30   $100

I know calling it next due date might not be so accurate if I end up with the date of a payment in that column, but I'm ok with that.

tl;dr : I want to find the next earliest date within each group, or the last date. How do I do this?

Answer 1

Well, maybe not the easiest way to approach this but I'd use VBA to build a function that returns the next due date (or largest date if next due date < Today). Array formulas are another option but they are rather inefficient.

1. Save your workbook as a .xlsm (Macro-enabled) file.
2. Select all values in the Vendor column and define a named range for them (the code below assumes you've called the named range "Vendor"). I don't recommend selecting the entire column because it will cause the code to run very slowly. Just select the cells with dates; the named range will dynamically expand if you insert new rows. To be safe you can select some extra cells at the bottom; including blank cells won't hurt anything.
3. Press ALT+F11 to open the VBA editor.
4. From the "Insert" menu, select "Module".

5. Paste this VBA function:

   Public Function NextByVendor(ByVal rngVendor As Range)
   
   Dim c As Range
   Dim strVendorName
   Dim lngToday As Long
   Dim lngNextDate As Long
   
   lngToday = Int(CDbl(Now()))
   lngNextDate = 0
   
   strVendorName = rngVendor.Value
   
   'Get largest date value
   For Each c In Range("Vendor")
       If c.Value = strVendorName And _
       c.Offset(0, 1).Value > lngNextDate Then
           lngNextDate = c.Offset(0, 1).Value
       End If
   Next c
   
   'If largest date is > today, get next available date
   If lngNextDate > lngToday Then
       For Each c In Range("Vendor")
           If c.Value = strVendorName And _
           c.Offset(0, 1).Value < lngNextDate And _
           c.Offset(0, 1).Value > lngToday Then
               lngNextDate = c.Offset(0, 1).Value
           End If
       Next c
   End If
   
   NextByVendor = lngNextDate
   
   End Function
   

6. Save and close the VBA window. Return to your spreadsheet and add a new column to the right of your data. In the first data row (say it's 2), enter the formula "=NextByVendor(A2)".

7. Copy the formula all the way down. You should end up with something like this:

   Vendor          Date            Invoice   Payment   Next Payment Due
   Vendor A        6/30/2012       $200.00             10/30/2012
   Vendor A        6/30/2012                 ($200)    10/30/2012
   Vendor B        7/5/2012        $500.00             7/5/2012
   Vendor B        7/5/2012                  ($500)    7/5/2012
   Vendor C        10/28/2012      $50.00              10/28/2012
   Vendor A        10/30/2012      $100.00             10/30/2012
   Vendor C        11/15/2012      $50.00              10/28/2012
   

8. Refresh your PivotTable; the new data should now be available to it. You can set the value type for "Next Payment Due" in the PivotTable to "Average" or "Min".

Hope this helps!

-- jm