Excel Spreadsheet for vehicle maintenance

[Phildux]

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

 

[barry houdini]

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

 

[Phildux]

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

 

[barry houdini]

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?

 

[Phildux]

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

 

[barry houdini]

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

garage.xls
	A	B	C	D	E
1	Vehicle	inspection 1	inspection 2	inspection 3	inspection 4
2
3	ZX342	24 Dec 04	11 Feb 05
4	sf2536	03 Jan 05	13 Feb 05	27 Mar 05
5	gd7635	08 Dec 04	12 Feb 05	08 Apr 05
6	er2635	01 Apr 05
7	erd342	02 Oct 04	01 Jan 05	20 Feb 05
8	wsq59	13 Dec 04	02 Feb 05	31 Mar 05
9	asg653
10	ewp124	16 Feb 05	31 Mar 05
11
Service History

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

garage.xls
	A	B	C	D	E
1	Vehicle	inspection period	last inspection	next inspection due
2
3	ZX342	8 weeks	11 Feb 05	08 Apr 05
4	sf2536	6 weeks	27 Mar 05	08 May 05
5	gd7635	8 weeks	08 Apr 05	03 Jun 05
6	er2635	8 weeks	01 Apr 05	27 May 05
7	erd342	8 weeks	20 Feb 05	17 Apr 05
8	wsq59	6 weeks	31 Mar 05	12 May 05
9	asg653	8 weeks	none
10	ewp124	8 weeks	31 Mar 05	26 May 05
11
Master

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

 

[TomE]

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

http://office.microsoft.com/en-us/templates/TC061061511033.aspx
http://office.microsoft.com/en-us/templates/TC010186481033.aspx

 

[Phildux]

EXCELLENT stuff many thanks

 

[spuri]

Hi

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

Any help would be appreciated!!

 

[cjriss]

Barry - this looks almost like what I need. I'm trying to track the new service requirement by the number of miles that has passed. I have three different things I need p one every 100,000 miles, one every 5,000 miles and one every 2,500 miles. I'm new to this site so hope it's okay to reply to this thread for this question. Any help you could give would be appreciated. Also - will the formulas from here continue to work if I copy and paste them into a spreadsheet?