Vehicle and Trailer inspection sheets

[David Jones]

Hi All,

I need to carry out 6 weekly and 10 weekly inspections on our units and trailers. How do I set up the sheets so that:-
1) It automatically records the next date of Inspection and respective dates for the next 6 months so that I have a list of due and completed inspections.
2) On the 2nd page/sheet, I want to have the the identical list of trailers, but instead of showing the inspections as being due in either 6 weeks or 10 weeks, I want to be able to see what vehicle and/or trailer is currently (within the next 7 days) due inspection


Thank you

David

 

[Jeffrey Mahoney]

You need two databases. One to record the history of inspections per trailer and one to keep the status of the trailer. The status DB is going to show the last inspection and the next inspection date. You can create Conditional Formatting on the Status DB to show upcoming inspections with a certain date range. You can filter and sort the Status DB so it shows a chronological upcoming inspections list.

Take it one step at a time and ask for help.

Welcome to the site.

Jeff

 

[David Jones]

Hi Jeff,

Thank you for your reply. I can use Excel, but I am very much a novice when it comes to setting up databases,etc. I most certainly will need a lot of help.

Thank you

David

You need two databases. One to record the history of inspections per trailer and one to keep the status of the trailer. The status DB is going to show the last inspection and the next inspection date. You can create Conditional Formatting on the Status DB to show upcoming inspections with a certain date range. You can filter and sort the Status DB so it shows a chronological upcoming inspections list.

Take it one step at a time and ask for help.

Welcome to the site.

Jeff

 

[Jeffrey Mahoney]

A database (DB) is nothing more than a table on a sheet. Each row is a record. Columns of data are called fields. Usually, the left most column is used for a unique field that identifies each trailer or piece of equipment; no duplicates. It could be a serial number or VIN. A Short name field is helpful and so is a description field. What fields you put after that is up to you. You can add more later.

In your case, you wanted to track when a trailer needs inspection. If you have different types of inspections, then you will need different fields to indicate completion of the last inspection and next inspection for each type. You could include a field to indicate the period of time between each inspection. You might want to include the result of the last inspection. You might want to include a field to indicate special requirements for the next inspection. This "Inspection Status" DB will change all the time. Meaning, you won't have multiple lines for each trailer as record, each record will change as an inspection is completed.

The other DB you need, I think, is an "Inspection History" DB. Setup the same way as the other, but the Unique identifier field will have duplicates. Because you're just maintaining a historical inspection db. You won't need the fields that indicate the next inspection date.

Next, you can learn how to filter the data and create conditional formatting on the inspection dates to indicate upcoming inspections. I can show you how to create formulas to count the number of upcoming inspections. We can create Date Range Cells or length of time to control the variable for the conditional formatting. I can show you how to create VBA code that will automatically filter the inspections based on the Date Range.

Get your databases created.

Jeff

 

[David Jones]

Thanks Jeff
I appreciate your help.
I have created my data base and presumably, will mirror database1 in sheet 2. With the exception of future dates, only actually completed inspection dates. Will this automatically copy from sheet1 to sheet2 on completion of the inspection.
I would also like every second inspection to include a brakes test, is this possible to show?
I am at 1st base ( with the data base completed), can you please get me to 2nd base?

Many thanks

David

 

[Jeffrey Mahoney]

I'm still with you. OK, it would be nice to have a list of your fields. Can you type out your column headers for each DB please.

Concerning your brakes test inspections. As I said before, you can have multiple inspection types in your Inspection Status DB. For instance:

Excel 2013/2016

	A	B	C	D	E	F	G	H	I
1	Item #	Last Tire Date	Next Tire Insp Date	Last Tire Result	Next Tire Needs	Last Break Date	Next Break Date	Last Break Result	Next Break Needs
2	TR14778	1/1/2019	6/1/2019	@ 30% tread	Look for side wall wear, Uneven wear on right inside	1/1/2019	8/1/2019	Pads on both sides at 50%	Check drum parts

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

When all data is in, you can filter the data by inspection dates; you could select to filter by the next tire inspection or by the next break inspection. I would add a column that calculates the earliest date for all inspections so you can filter all the inspections by date range.

To answer your question about getting data from one DB to another. I would create some macros to help with this or make some formulas on the Inspection Status DB. Your Inspection History DB should be the master data set, IMO. You could go a couple ways. We can create formulas to find the last date of inspection for each inspection type. You could have a field on the Inspection Status DB that allows you to set the period of time between inspections, then simply calculate the next inspection date. Do you usually work in Months, Weeks, or Days?

Jeff

 

[David Jones]

I'm still with you. OK, it would be nice to have a list of your fields. Can you type out your column headers for each DB please.

Concerning your brakes test inspections. As I said before, you can have multiple inspection types in your Inspection Status DB. For instance:

Excel 2013/2016

	A	B	C	D	E	F	G	H	I
1	Item #	Last Tire Date	Next Tire Insp Date	Last Tire Result	Next Tire Needs	Last Break Date	Next Break Date	Last Break Result	Next Break Needs
2	TR14778	1/1/2019	6/1/2019	@ 30% tread	Look for side wall wear, Uneven wear on right inside	1/1/2019	8/1/2019	Pads on both sides at 50%	Check drum parts

<tbody>
</tbody>

Sheet1​

When all data is in, you can filter the data by inspection dates; you could select to filter by the next tire inspection or by the next break inspection. I would add a column that calculates the earliest date for all inspections so you can filter all the inspections by date range.

To answer your question about getting data from one DB to another. I would create some macros to help with this or make some formulas on the Inspection Status DB. Your Inspection History DB should be the master data set, IMO. You could go a couple ways. We can create formulas to find the last date of inspection for each inspection type. You could have a field on the Inspection Status DB that allows you to set the period of time between inspections, then simply calculate the next inspection date. Do you usually work in Months, Weeks, or Days?

Jeff

Hi Jeff,

Below is the type of layout that I refer to and this is only a part of it. Can you work with this?

Regards,

David

Trailer	Chassis No	MOT date	Year	Tail Lift Lola Test	Inspection Date	Inspection Date	Inspection Date	Inspection Date
MTT100SB	C312228	Feb-20	2010		04/04/2019	16/05/2019	27/06/2019	08/08/2019
MTT02	C241030	Nov-19	2007		15/03/2019	26/04/2019	07/06/2019	19/07/2019
MTT20	C300808	Jan-20	2012		01/04/2019	10/06/2019	19/08/2019	28/10/2019
MTT34	C346051	Jul-19	2012		04/03/2019	13/05/2019	22/07/2019	30/09/2019
MTT37	C356564	Nov-19	2013		15/04/2019	24/06/2019	02/09/2019	11/11/2019
MTT38	C356563	Oct-19	2013		01/04/2019	10/06/2019	19/08/2019	28/10/2019
MTT41	C361685	Feb-20	2014		21/02/2019	02/05/2019	11/07/2019	19/09/2019
MTT42 (2020)	C361720	Mar-19	2014		19/03/2019	28/05/2019	06/08/2019	15/10/2019
MTT43	C361681	Mar-19	2014		10/04/2019	19/06/2019	28/08/2019	06/11/2019
MTT44	C361682	Mar-19	2014		04/02/2019	15/04/2019	24/06/2019	02/09/2019
MTT45	C361721	Mar-20	2014		28/02/2019	09/05/2019	18/07/2019	26/09/2019
MTT46	C361684	Apr-19	2014		10/04/2019	19/06/2019	28/08/2019	06/11/2019
MTT47	C361683	Feb-20	2014		12/02/2019	23/04/2019	02/07/2019	10/09/2019
MTT48	C361680	Mar-20	2014		25/02/2019	06/05/2019	15/07/2019	23/09/2019
MTT49	C361686	Mar-19	2014		06/03/2019	15/05/2019	24/07/2019	02/10/2019
MTT50	C361687	Mar-20	2014		04/03/2019	13/05/2019	22/07/2019	30/09/2019
MTT51	C253752	Aug-19	2008		25/03/2019	06/05/2019	17/06/2019	29/07/2019
MTT52	C253755	Jul-19	2008		19/03/2019	30/04/2019	11/06/2019	23/07/2019
MTT53	C253753	Aug-19	2008		18/03/2019	29/04/2019	10/06/2019	22/07/2019
MTT68	C374154	Sep-19	2014		01/04/2019	10/06/2019	19/08/2019	28/10/2019
MTT69	C374155	Sep-19	2014		22/04/2019	01/07/2019	09/09/2019	18/11/2019
MTT58DD	C234717	Nov-19			21/01/2019	01/04/2019	10/06/2019	19/08/2019
MTT72DD	C234723	Sep-19			26/03/2019	04/06/2019	13/08/2019	22/10/2019
MTT76DD	C234733	Nov-19			15/03/2019	24/05/2019	02/08/2019	11/10/2019
MTT79DD	C300575	Oct-19			25/04/2019	04/07/2019	12/09/2019	21/11/2019
MTT80DD	C301387	Nov-19			08/01/2019	19/03/2019	28/05/2019	06/08/2019
MTT81DD	C303015	Jun-19			09/01/2019	20/03/2019	29/05/2019	07/08/2019
MTT82DD	C303027	Aug-19			11/03/2019	20/05/2019	29/07/2019	07/10/2019
MTT83DD	C303038	Mar-19			16/03/2019	25/05/2019	03/08/2019	12/10/2019
MTT84DD	C301089	Mar-20			03/03/2019	12/05/2019	21/07/2019	29/09/2019
MTT86DD	C303028	Jun-19			07/03/2019	16/05/2019	25/07/2019	03/10/2019
MTT87DD	C301249	Nov-19			11/03/2019	20/05/2019	29/07/2019	07/10/2019
MTT88DD	C301400	Jul-19			27/02/2019	08/05/2019	17/07/2019	25/09/2019
MTT89DD	C303017	May-19			01/04/2019	10/06/2019	19/08/2019	28/10/2019
MTT90DD	C303021	Jul-19			11/04/2019	20/06/2019	29/08/2019	07/11/2019
MTT91DD	C303055	Jul-19			27/03/2019	05/06/2019	14/08/2019	23/10/2019
MTT92SK	C390485	Feb-20	2015		12/02/2019	23/04/2019	02/07/2019	10/09/2019
MTT93SK	C390488	Mar-20	2015		06/03/2019	15/05/2019	24/07/2019	02/10/2019
MTT94SK	C390487	Mar-20	2015		14/03/2019	23/05/2019	01/08/2019	10/10/2019
MTT95SK	C390486	Mar-19	2015		15/01/2019	26/03/2019	04/06/2019	13/08/2019
MTT96SK	C390489	Feb-20	2015		06/02/2019	17/04/2019	26/06/2019	04/09/2019
MTT97SK	C390490	Jan-20	2015		29/01/2019	09/04/2019	18/06/2019	27/08/2019
MTT98SB	C391894	Jun-19	2015		13/03/2019	22/05/2019	31/07/2019	09/10/2019
MTT99SC	C385981	Jun-19	2015		06/03/2019	15/05/2019	24/07/2019	02/10/2019
S1	C187979	Oct-19	2005		22/04/2019	03/06/2019	15/07/2019	26/08/2019
S2	C187980	Oct-19	2005		29/04/2019	10/06/2019	22/07/2019	02/09/2019
S3	C187984	Jul-19	2005		01/04/2019	13/05/2019	24/06/2019	05/08/2019
S5	C187986	Oct-19	2005		14/03/2019	25/04/2019	06/06/2019	18/07/2019
S6	C187987	Nov-19	2005		26/03/2019	07/05/2019	18/06/2019	30/07/2019
S7	C187988	Nov-19	2005		29/03/2019	10/05/2019	21/06/2019	02/08/2019
S8	C187990	Dec-19	2005		23/04/2019	04/06/2019	16/07/2019	27/08/2019
S10	C187992	Dec-19	2005		09/02/2019	23/03/2019	04/05/2019	15/06/2019
S14	C188606	Oct-19	2005		22/04/2019	03/06/2019	15/07/2019	26/08/2019
S15	C188607	Nov-19	2005		26/03/2019	07/05/2019	18/06/2019	30/07/2019
SB2001	C292703	Aug-19	2009		14/01/2019	25/02/2019	08/04/2019	20/05/2019
SB2002	C292704	Aug-19	2009		22/02/2019	05/04/2019	17/05/2019	28/06/2019
SB2003	C292708	Sep-19	2009		02/04/2019	14/05/2019	25/06/2019	06/08/2019

<colgroup><col span="4"><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[David Jones]

Hi Jeff,

I forgot to mention that inspection dates vary according to the age of the trailer. So dates range from 6 weekly to 10 weekly inspections. Some are even at 12 weeks (Hire trailers).

Kind regards,

David

 

[Jeffrey Mahoney]

David,

What are the 4 different columns of inspection dates?

Jeff

 

[Jeffrey Mahoney]

I tried to paste the data into Excel. Not a good format. Have you looked at the forum tools you can download? I use the Mr Excel HTML Maker to copy worksheet data. Very easy to use.