+ Reply to Thread
Results 1 to 5 of 5

find min and max date for each vehicle inspection, when there are several dates

  1. #1
    Registered User
    Join Date
    03-05-2018
    Location
    GENEVA, SWITZERLAND
    MS-Off Ver
    10
    Posts
    18

    Question find min and max date for each vehicle inspection, when there are several dates

    Hello! I have a large dataset with vehicle plates and their status = "being inspected" by date. So the dataset can look similar to this:

    Plate Number Status Status Date

    15787987 Inspection 01/09/2022
    15787987 Inspection 02/09/2022
    15787987 Inspection 03/09/2022
    15787987 Inspection 04/09/2022
    15787987 Inspection 05/09/2022
    15787987 Inspection 01/10/2023
    15787987 Inspection 02/10/2023
    15787987 Inspection 10/10/2019
    15787987 Inspection 11/10/2019
    15787987 Inspection 12/10/2019
    45484848 Inspection 04/09/2018
    45484848 Inspection 05/09/2018
    45484848 Inspection 01/10/2017
    45484848 Inspection 02/10/2017
    45484848 Inspection 13/06/2016
    45484848 Inspection 14/06/2016
    45484848 Inspection 15/06/2016
    45484848 Inspection 16/06/2016
    .
    ..
    .
    .
    .

    How can I get a list of plates with all the dates when they've been inspected as follows?

    15787987 01/09/2022 - 05/09/2022
    15787987 01/10/2023 - 02/10/2023
    15787987 10/10/2019 - 12/10/2019
    45484848 04/09/2018 - 05/09/2018
    45484848 01/10/2017 - 02/10/2017
    45484848 13/06/2016 - 16/06/2016

    So far i have tried to use the formulas:

    =MIN(IF(Plates!$A$1:$A$71436=$A2,Plates$C$1:$C$71436)) --> so far it has worked, but just providing the following results:

    15787987 10/10/2022 - 02/10/2023
    45484848 13/06/2016 - 05/09/2018

    which means those vehicles have been under inspection for 2 years each...

    any clues on this? thanks a lot in advance!

    Laura

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,050

    Re: find min and max date for each vehicle inspection, when there are several dates

    Assuming your data is sorted as in your example, you could add an additional column that checks if each row's plate number is the same as the one above and the date if the day after the row above. If so, it returns the same number as the row above; if not it adds 1. Then just make a pivot table with plate number and the new column in the row fields, and min status date and max status date as the value fields.
    Example attached.
    Attached Files Attached Files
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,887

    Re: find min and max date for each vehicle inspection, when there are several dates

    Formula solution here. Generous amounts of helper columns makes it easy to follow.
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,852

    Re: find min and max date for each vehicle inspection, when there are several dates

    Are you using Excel 2010? Or is that a Windows version?
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,123

    Re: find min and max date for each vehicle inspection, when there are several dates

    Quote Originally Posted by laura.gomper View Post
    MS-Off Ver: 10
    10 is not an Excel or Office version. Please update your profile to show the version name (like Microsoft 365, Excel 2019, etc.)

    $B Excel version.jpg
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Re-occuring Inspection Dates
    By Bill Kuunders in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 02:05 PM
  2. Re-occuring Inspection Dates
    By Belarny Mic in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 11:05 AM
  3. Re-occuring Inspection Dates
    By Bill Kuunders in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 06:05 AM
  4. Re-occuring Inspection Dates
    By Bill Kuunders in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 04:05 AM
  5. Re-occuring Inspection Dates
    By Belarny Mic in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  6. Re-occuring Inspection Dates
    By Belarny Mic in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. [SOLVED] Re-occuring Inspection Dates
    By Belarny Mic in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  8. [SOLVED] Re-occuring Inspection Dates
    By Belarny Mic in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1