# vba - pulling and comparing dates

#### manwithaquestion

##### New Member
Need some help with this logic..

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 inputed in there.

Example: Tab 1:

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

Customer2 Id2 scheduled visit </CODE>

Tab 2:

<CODE>Billy Joe 12345 03/15/14

Billy Joe 12345 04/15/14

Billy Joe 12345 1/02/15

</CODE>So I would want the date "04/15/14" and not the other two..

EDIT: I have a partial equation that works for MOST customers... the problem 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 (if that makes sense..)

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:

<CODE>6/3/2015
6/18/2015
6/26/2015
6/9/2015

</CODE>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 "next visit".

I need a check within my statement that makes sure that the scheduled date must be less than the value it pulls for that customer id in the other tab. I know this is a lot and maybe hard to understand but just ask some clarification questions and I can answer them. This is a recurring document to where when I click "refresh" it just pulls in all the data for today...

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### manwithaquestion

##### New Member
EDIT: the 2nd to last sentence should say "Notice how...their scheduled visits for that month would be less than their "last visit"

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,632
Messages
5,838,474
Members
430,549
Latest member
jayjay2022

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back