Results 1 to 9 of 9

Excel Spreadsheet for vehicle maintenance

This is a discussion on Excel Spreadsheet for vehicle maintenance within the Excel Questions forums, part of the Question Forums category; hello i would like to make up an excel spreadshheet for our vehicle maintenance. some are are inspected at 6 ...

  1. #1
    Board Regular
    Join Date
    Apr 2005
    Location
    United Kingdom
    Posts
    61

    Default Excel Spreadsheet for vehicle maintenance

    hello i would like to make up an excel spreadshheet for our vehicle maintenance. some are are inspected at 6 weeks and others are 8 weeks. i would like ecxel to high light the due dates 7 days before the inspection dates are due is this possible? also to cindiser are test dates ect any help would be greatly appreciated

  2. #2
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,971

    Default

    Say you have last inspection date in A2 and B2 says either "6 weeks" or "8 weeks" then in C2 this formula

    =A2+LEFT(B2)*7

    will give you the next inspection date

    If you select C2 and use Format > Conditional Formatting

    "Formula is" and set to

    =C2-TODAY()<8

    and select your desired format

    This will highlight C2 if it's 7 days or less away

  3. #3
    Board Regular
    Join Date
    Apr 2005
    Location
    United Kingdom
    Posts
    61

    Default

    sound ok but these vehicle have approx 8 inspections per year at 6 weekly inintervals on a spreadsheet would we be able to show all service intervals for all vehicles and still show which ones are due

  4. #4
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,971

    Default

    Your best bet might be to have the inspection records for all your vehicles on one sheet.

    One row per vehicle with the date of each inspection entered in the next available column.

    On another sheet you can show each vehicle again and have a column which will automatically pick up the date of the last inspection for each vehicle and another column which will show the due date for the next inspection.

    In that way you can have one column for all the due dates and format it so that those due in the next week are highlighted.

    Once an inspection is done the date is entered on your first sheet and the due date will update automatically.

    Does that sound feasible - I could post an example this evening?

  5. #5
    Board Regular
    Join Date
    Apr 2005
    Location
    United Kingdom
    Posts
    61

    Default

    sound just perfect please please could you post an example.
    many many thanks for all your help

  6. #6
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,971

    Default

    I created a workbook with two sheets - the first is Service History

    ******** ******************** ************************************************************************>
    Microsoft Excel - garage.xls___Running: 11.0 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    1
    Vehicleinspection 1inspection 2inspection 3inspection 4
    2
    3
    ZX34224 Dec 0411 Feb 05
    4
    sf253603 Jan 0513 Feb 0527 Mar 05
    5
    gd763508 Dec 0412 Feb 0508 Apr 05
    6
    er263501 Apr 05
    7
    erd34202 Oct 0401 Jan 0520 Feb 05
    8
    wsq5913 Dec 0402 Feb 0531 Mar 05
    9
    asg653
    10
    ewp12416 Feb 0531 Mar 05
    11
    Service History

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.



    In A3

    =Master!A3

    and copied down column

    This just picks up vehicle ids from Master sheet - I presume you might use registrations

    Other entries should be self-explanatory - dates for each inspection for each vehicle. Dates can be in any format you want as long as excel recognises them as dates

    Next worksheet is Master

    ******** ******************** ************************************************************************>
    Microsoft Excel - garage.xls___Running: 11.0 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    1
    Vehicleinspection periodlast inspectionnext inspection due
    2
    3
    ZX3428 weeks11 Feb 0508 Apr 05
    4
    sf25366 weeks27 Mar 0508 May 05
    5
    gd76358 weeks08 Apr 0503 Jun 05
    6
    er26358 weeks01 Apr 0527 May 05
    7
    erd3428 weeks20 Feb 0517 Apr 05
    8
    wsq596 weeks31 Mar 0512 May 05
    9
    asg6538 weeksnone
    10
    ewp1248 weeks31 Mar 0526 May 05
    11
    Master

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.



    Vehicle IDs and inspection periods in columns A and B

    In C3 and copied down column

    =IF('Service History'!B3="","none",LOOKUP(9.99999999999999E+307,'Service History'!3:3))

    which shows the last inspection for each vehicle (or "none" if there are none)
    as shown on "Service History" sheet

    In D3 and copied down column

    =IF(C3="none","",C3+LEFT(B3)*7)

    which shows the date of the next inspection (or is blank if there have been no inspections for that vehicle) based on the number of weeks in column B.

    You can conditionally format this column to highlight inspections due in the next week

    Select column D

    Use Format > Conditional Formatting

    Use "Formula Is"

    =AND(D1<>"",D1-TODAY()<7)

    and select desired formatting

    If you wished you could add additional conditional formatting to highlight, for instance, if the inspection date has passed(!) or if the inspection due date is less than 2 days away

    Hope this helps

  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    28

    Default

    You might want to consider using and/or modifying an existing template. Here are two possibilities:

    http://office.microsoft.com/en-us/te...061511033.aspx
    http://office.microsoft.com/en-us/te...186481033.aspx

  8. #8
    Board Regular
    Join Date
    Apr 2005
    Location
    United Kingdom
    Posts
    61

    Default

    EXCELLENT stuff many thanks

  9. #9
    New Member
    Join Date
    Dec 2010
    Posts
    5

    Default Re: Excel Spreadsheet for vehicle maintenance

    Hi

    I'm also looking for an excel spreadsheet for vehicle maintenance but none of the hyper-links work.

    Any help would be appreciated!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com