Beau the dog
Board Regular
- Joined
- Mar 8, 2021
- Messages
- 74
- Office Version
- 365
- Platform
- 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...
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!!
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.
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 | ### | |
