HI,
I have a list of journeys made by a person and need to know if they had a valid pass in the same month. I have one sheet which shows the journeys in the following format;
Column A: Year
Column B: Month
Column H: PersonID
In a separate sheet I have a list of Pass Expiry dates and person details like so;
Column I: PersonID
Column J: Pass expiry date
All passes are active for 1 year so the start date could be calculated from the expiry date if required.
The problem I'm having is how to determine if a the person making a journey in a given year/month combination also had a valid pass. If there was only one pass expiry date by person I could use a vlookup and check if the date was within a given range. However, an individual will have a number of passes (renewals). For the purposes of determining if they had a valid pass, I'm taking it as if the pass was valid for any day in the month then it was valid for the whole month as my journey details are aggregated to month.
Any help is greatly appreciated.
Thanks
Alex
Bookmarks