vba - pulling and comparing dates

manwithaquestion

New Member
Joined
Jun 19, 2015
Messages
2
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...
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

manwithaquestion

New Member
Joined
Jun 19, 2015
Messages
2
EDIT: the 2nd to last sentence should say "Notice how...their scheduled visits for that month would be less than their "last visit"
 

Watch MrExcel Video

Forum statistics

Threads
1,127,185
Messages
5,623,260
Members
415,958
Latest member
pandagurl

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top