Hi Team.
Another strange one that I should be able to figure, but is not so simple.
I have an editable spreadsheet that tracks the last time a driver went over a particular route. This spreadsheet then uses todays date to reset the counter for expiry. For example if Driver Bloggs went from Station A to Station B and the last time he went over that route was 6 months ago, entering in todays date will reset the counter to 0 therefore he remains current on that route. The aim of the spreadsheet is to flag if he is nearing expiry or has expired. For example if he had not run the route from A to B within 9 months of todays date he is nearing expiry. If they have not run the route in 12 months and 1 day, they have expired and need reassessing. Works great. No problems there.
My problem is that I also now need to record the amount of times that they have run the route. This will start at 1 the first time they run the route and increase to infinity on each run irrespective of qualification.
I thought that the easiest way was to enter the number of times a route has been run manually into a reference cell. Say Cell B1 starts at 0 for an unqualified driver. When they run the route (qualified or not) the date they run the route is entered in A1. For arguments sake the initial date in A1 will be 01/01/1900. Assume then that they run the route today and a new date gets entered into A1 changing the date to 20/03/18. I need Cell B1 to look at Cell A1 and realise that the date has changed from 01/01/1900 to something different then increase itself by 1 (for 1 trip). If therefore they were to run the same trip tomorrow, the user overwrites the date in A1 (currently 20/03/18) to 22/03/18 and B2 incerases again by 1. B2=2.
Preference is not to use a macro as this is a large spreadsheet with nearly 100 drivers and 15 different routes to track. Helper olumnareok as the wokings are all hidden.
Any suggesion?
Another strange one that I should be able to figure, but is not so simple.
I have an editable spreadsheet that tracks the last time a driver went over a particular route. This spreadsheet then uses todays date to reset the counter for expiry. For example if Driver Bloggs went from Station A to Station B and the last time he went over that route was 6 months ago, entering in todays date will reset the counter to 0 therefore he remains current on that route. The aim of the spreadsheet is to flag if he is nearing expiry or has expired. For example if he had not run the route from A to B within 9 months of todays date he is nearing expiry. If they have not run the route in 12 months and 1 day, they have expired and need reassessing. Works great. No problems there.
My problem is that I also now need to record the amount of times that they have run the route. This will start at 1 the first time they run the route and increase to infinity on each run irrespective of qualification.
I thought that the easiest way was to enter the number of times a route has been run manually into a reference cell. Say Cell B1 starts at 0 for an unqualified driver. When they run the route (qualified or not) the date they run the route is entered in A1. For arguments sake the initial date in A1 will be 01/01/1900. Assume then that they run the route today and a new date gets entered into A1 changing the date to 20/03/18. I need Cell B1 to look at Cell A1 and realise that the date has changed from 01/01/1900 to something different then increase itself by 1 (for 1 trip). If therefore they were to run the same trip tomorrow, the user overwrites the date in A1 (currently 20/03/18) to 22/03/18 and B2 incerases again by 1. B2=2.
Preference is not to use a macro as this is a large spreadsheet with nearly 100 drivers and 15 different routes to track. Helper olumnareok as the wokings are all hidden.
Any suggesion?