Automated vehicle service planner

[Beau the dog]

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!!

Registration

A

B

LOLER

Last A service

WC

Last B service

WC

Last C service

WC

Last LOLER

WC

03/05/2021

10/05/2021

17/05/2021

24/05/2021

31/05/2021

07/06/2021

14/06/2021

21/06/2021

28/06/2021

A

6

18

26

02/04/2021

###

04/11/2021

###

07/05/2020

###

18/12/2020

###

B

6

18

26

09/04/2021

###

11/11/2021

###

14/05/2020

###

10/01/2021

###

C

6

18

26

16/04/2021

###

18/11/2021

###

21/05/2020

###

13/01/2021

###

D

6

18

26

23/04/2021

###

25/11/2021

###

28/05/2020

###

13/12/2020

###

E

6

18

26

30/04/2021

###

02/12/2021

###

04/06/2020

###

21/01/2021

###

F

6

18

26

09/04/2021

###

09/12/2021

###

11/06/2020

###

26/11/2020

###

G

6

18

26

16/04/2021

###

16/12/2021

###

18/06/2020

###

26/01/2021

###

​

 

[Beau the dog]

Please see screenshot example of how my planner is displayed

 

[Snakehips]

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 ?

 

[Beau the dog]

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!!