Automated vehicle service planner

Beau the dog

Board Regular
Joined
Mar 8, 2021
Messages
72
Office Version
  1. 365
Platform
  1. Windows
Good morning all,

I created an automated service planner, whereby the user just inputs the last 'A Service', 'B Service', 'C Service' and LOLER date....and the planner auto populates with future Services for the next 54 weeks. I've used LOADS of helper columns and ONE helper tab to get there, and it works....just! I feel I've gone round the houses to get there and thought some of you Excel guru's may have a better (more streamlined!) solution.

So I've drawn up an example below to see if you can help. Let me explain...
  • Registration header is cell A1
  • the next 3 columns are the service weekly intervals, so an 'A Service every 6 weeks, B every 18 weeks and LOLER every 26 weeks. C service is an MOT so we know that's 52 weeks. I've used cell references for this as the intervals can change.
  • I've entered some service dates and the WC column next to each date is a simple formula to return the Monday for the service date, as the planner is always weekly.
  • The service schedule goes A-A-B A-A-B A-A-B A-A-B (hence the B service being 18 week interval, to align with the A services)
  • But whenever there is a C service, it resets back to A-A-B six weeks after
  • The planner can display just the letters, either A, B, C but can only show the one, the highest letter. So if and A & B were on the same day the cell would say B, and C overrides the A and B.
  • If a B service does fall between two A service's it needs to join to the closest A and then display a B
  • If a C service falls between two A services it needs to join with the earliest date only, and display a C (To avoid going past the MOT expiry date)
  • LOLER is separate, it falls where it falls, so if it lands on the same day as a B for example...the cell will show B / LOLER
  • So basically every six weeks there is an event, either an A, B or a C...and the LOLER's falls where it falls.
  • The planner starts on column M and on the one I created if I change the date in Cell M1, the future dates all change and the planner changes with it.
so for example: A-A-B-A-A-B-A-A-C-A-A-B-A-A-B or A-A-B-A-A-B-A-C-A-A-B-A-A-B
Both examples reset to A-A-B after the C service.

Hopefully I've explained this well enough and either challenged some of you.... or probably made you all just move on to the next thread!!


RegistrationABLOLERLast A serviceWCLast B serviceWCLast C serviceWCLast LOLERWC03/05/202110/05/202117/05/202124/05/202131/05/202107/06/202114/06/202121/06/202128/06/2021
A6182602/04/2021###04/11/2021###07/05/2020###18/12/2020###
B6182609/04/2021###11/11/2021###14/05/2020###10/01/2021###
C6182616/04/2021###18/11/2021###21/05/2020###13/01/2021###
D6182623/04/2021###25/11/2021###28/05/2020###13/12/2020###
E6182630/04/2021###02/12/2021###04/06/2020###21/01/2021###
F6182609/04/2021###09/12/2021###11/06/2020###26/11/2020###
G6182616/04/2021###16/12/2021###18/06/2020###26/01/2021###
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Please see screenshot example of how my planner is displayed
 

Attachments

  • Planner screen shot.PNG
    Planner screen shot.PNG
    24.6 KB · Views: 11
Upvote 0
Good news bad news, who knows, but I've actually been sad enough to take a look at this.;)
I'm instantly confused by your dates.
Eg Reg A If the last A was 2/4/21 and the last B was 4/11/21 and C and LOLER were back in 2020, did Reg A go without any service for 7 months ?
 
Upvote 0
Good news bad news, who knows, but I've actually been sad enough to take a look at this.;)
I'm instantly confused by your dates.
Eg Reg A If the last A was 2/4/21 and the last B was 4/11/21 and C and LOLER were back in 2020, did Reg A go without any service for 7 months ?

Hi, these were just dummy dates to show what the data input is like. So I didn't put much thought into them...trust me I do in the real world...its my job!!
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top