Excel help please

**V8-Powered** · 16-07-12, 07:31

Question for the Excel experts.....

Running an Excel sheet in a Gantt format

Have a fleet of 30 vehicles that have maintenance stipulated at either 38 days or 20,000 miles, whichever comes first.
Currently running the sheet on days only with conditional formatting flagging due dates. Would also like to add the mileage stipulation to the same sheet flagging the days or miles trigger but my raddled mind will not help me.

Anyone help please?

**V8-Powered** · 16-07-12, 15:12

Anyone.....

?

**Olly** · 16-07-12, 15:29

Need a little more information about what data is stored on your worksheet (oh, and your Excel version would be handy too, as conditional formatting changed quite a bit in more recent versions).

Let's make a little assumption about your workbook, and suggest Columns A : D contain Vehicle Reg, Last Service Date, Last Service Milage and Current Mileage. In this case, the conditional formatting formula would be something like:

Code:

=OR((TODAY()-$B2)>=38,($D2-$C2)>=20000)

Hopefully that helps you enough to work it into your worksheet - if not, give us a bit more info about how it's structured

**V8-Powered** · 16-07-12, 20:44

Originally Posted by Olly

Need a little more information about what data is stored on your worksheet (oh, and your Excel version would be handy too, as conditional formatting changed quite a bit in more recent versions).

Let's make a little assumption about your workbook, and suggest Columns A : D contain Vehicle Reg, Last Service Date, Last Service Milage and Current Mileage. In this case, the conditional formatting formula would be something like:

Code:

=OR((TODAY()-$B2)>=38,($D2-$C2)>=20000)

Hopefully that helps you enough to work it into your worksheet - if not, give us a bit more info about how it's structured

Thanks for replying Olly....

Excel 2007.

Column A is the vehicle ID - 30 vehicles (rows) in total
Column B is date of last examination
Column C is planned date
Column D is back-stop date (38 days from last examination)
Column E onwards is a 31-day view of the maintenance in a Gantt format

All inputting is currently a manual operation.
Planning to import miles to next exam in to sheet and have it flag by conditional formatting when either days or mileage approaches either 38 days or 20,000 miles.

Hope that helps a little?

**Mussels** · 18-07-12, 11:22

Gantt charts are not a supported feature in Excel but can be bodged using stacked charts, as it is a bodge could there be a better way of showing it?
You're using it to forecast maintenance, are you entering daily mileage to try and forecast when the inspection date is being brought forward?
This sounds like it would be better done in a database with tables to keep historic data to help with predictions, it can be done in excel but will get a bit unwieldy.

**V8-Powered** · 22-07-12, 19:11

Originally Posted by Mussels

Gantt charts are not a supported feature in Excel but can be bodged using stacked charts, as it is a bodge could there be a better way of showing it?
You're using it to forecast maintenance, are you entering daily mileage to try and forecast when the inspection date is being brought forward?
This sounds like it would be better done in a database with tables to keep historic data to help with predictions, it can be done in excel but will get a bit unwieldy.

I'm open to any new way of making my life easier at work!

The 38 days is a maximum time the vehicles can run between maintenance but there is also a mileage cap of 20,000 miles too, so generally it is as you mention the mileage that dictates the maintenance to be brought forward.
Never set up a database from scratch so any suggestions / examples would be appreciated.....

**Mussels** · 22-07-12, 20:41

An access database (there are very different ones but don't worry about that) is like having several excel sheets in a workbook. One sheet for mileage, another for times and then different formulas set up to query each and get a meaningful result.
I should have a bit of spare time at work tomorrow, I'll try and knock something up.
How often do you check vehicle mileage?

**V8-Powered** · 22-07-12, 20:49

2007 I believe.

Any help would be much appreciated, thanks....

**Mussels** · 22-07-12, 20:54

Originally Posted by V8-Powered

2007 I believe.

Any help would be much appreciated, thanks....

I edited my post when I realised Olly had already asked.
I also added "how often do you record mileage?"

**V8-Powered** · 22-07-12, 20:59

Mileages for the fleet are updated daily - I generally just grab a column from an excel sheet listing all 30 vehicles and paste in

**jimbojetset** · 22-07-12, 21:30

Access is what you're after by the sounds of it then. I had some access training last week, something that used to take me half a day in excel is now half an hours work...nice...took a bit of time to set up the queries, but now they are done, import process deals with the cut and paste...boom. God knows why it has taken me so long to get to grips with access...it a piece of piss once you just get stuck in.