CODEWITHSUNDEEP
excelvbaif-statementexcel-formulaexcel-match
Cole Coen
Cole Coen

Reputation: 11

pulling and comparing dates in excel

For example:

On my first tab Last visit, I have columns, Customer Name, Customer # and Scheduled date

On my second tab, All visits I have Customer Name, Customer #, and Visit Dates. Now, this tab shows all of the visit dates (even the last one that is on Last visit) while the first tab just shows their last visit date and their next scheduled date.

Goal: On my first tab, Last Visit, I need some logic that compares the scheduled visit date with all of the visit dates.. for that specific customer ID. So on the next column after Scheduled date I want a new column Last visit date that compares the visit before the last visit date for that specific customer. So some customers have multiple visits, I need each cell for each customer (over 300 for this store) to read all of the times that they visited and only pull the max (most recent BUT NOT the one that is their last visit) date from the second tab All visits

The reason I want the second largest date of all their visits is because in the all visits tab, even their scheduled date is entered in there.

Example: Tab 1: 

Billy Joe    12345     1/02/15       <-- Scheduled visit    new column    --> visit before last visit

Custmer2      Id2       Date

EtcTab 2: 

  Billy Joe    12345     03/15/14

  Billy Joe    12345     04/15/14

  Billy Joe    12345     1/02/15

So I would want the date 4/15/14 and not the other two..

I have a partial equation that works for MOST customers.the problem with the equation is:

If a customer visits more than once per month, it still pulls the 2nd highest date of all his/her visits.. how do I make it check to make sure that the visit before the last visit has to be less than their last visit

Here's my equation:

{=LARGE(IF(('All visits'!B:B='Last visit'!B2),('All visits'!C:C)),2)}

B2 corresponds to the customer id (its matching their id on the other page, so when I drag it down it just continues to match the id's)...

An example of an issue:

6/3/2015
6/18/2015
6/26/2015
6/9/2015

Notice how there are multiple visits per month, so my equation will put 6/18/2015 in all of the spots even though their scheduled visits for that month would be less than their last visit.

Upvotes: 1

Views: 112

Answers (1)

chancea
chancea

Reputation: 5958

If I understand this correctly, all we need to do is add an extra array criteria in your LARGE function.

You already are getting the 2nd largest based on the customer ID. But we want the 2nd largest based on the customer Id and based on the scheduled date:

=LARGE(('All visits'!B:B='Last visit'!B1)*('All visits'!C:C>0)*(IF('All visits'!C:C<='Last visit'!C1,'All visits'!C:C)),2)
         ^^^^^^^^^ Customer ID  ^^^^^^^^    ^^ignore blanks^^    ^^^^ Only grab the dates that are <= last visit ^^^^   ^ 2nd largest

Edit to add additional checks for when the customer only has 1 visit date:

If we want it to display #N/A if there is less than two dates we have to use a SUMPRODUCT to get the total amount of entries:

SUMPRODUCT(('All visits'!B:B='Last visit'!B1)*('All visits'!C:C>0)*('All visits'!C:C<='Last visit'!C1))

We can then add that into an IF statement to return either NA() for the <2 case or our original formula for the >=2case:

=IF(SUMPRODUCT(('All visits'!B:B='Last visit'!B1)*('All visits'!C:C>0)*('All visits'!C:C<='Last visit'!C1))<2,NA(),LARGE(('All visits'!B:B='Last visit'!B1)*('All visits'!C:C>0)*(IF('All visits'!C:C<='Last visit'!C1,'All visits'!C:C)),2))

You can change the NA() to whatever fits your requirements

Upvotes: 1

Related Questions