Automated vehicle service planner

Beau the dog

New Member
Joined
Mar 8, 2021
Messages
31
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Beau the dog

New Member
Joined
Mar 8, 2021
Messages
31
Office Version
  1. 365
Platform
  1. Windows
Please see screenshot example of how my planner is displayed
 

Attachments

  • Planner screen shot.PNG
    Planner screen shot.PNG
    24.6 KB · Views: 5

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,355
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Mar 8, 2021
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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!!
 

Forum statistics

Threads
1,140,999
Messages
5,703,642
Members
421,307
Latest member
morrden86

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
Top