excel VBA script to work out next dates from complex dataset
Budget: £20 – £250 GBP
We have a list of jobs in a spreadsheet, each row has a due date and a completed date (if it has been completed) there are multiple jobs on the spreadsheet that have the same parent ID as a group whereby the jobs are part of a schedule i.e. Visit 1 / Visit 2 / Visit 3 / Visit 4.
If for instance Visit 1 is completed later than it's due date, then the due date of Visit 2 should change to a new offset based on the space between the visits as determined by the Parent.
e.g.
Parent Code (Series Code) is INSS123456
Parent Code type is 03-01
03-01 has 3 Months between visits
Visit 1 was due on 31/01/22
Visit 1 was completed late on 25/02/22
Visit 2 is originally due on 30/04/22
Visit 2 new due date should now be 25/05/22
Visit 3 new due date should now be 25/08/22
and so on
I'm looking for someone to be able to write some simple code that will identify those visits not done on time (or earlier than the month that they were due in) and revise the due dates accordingly.
This needs to be repeatable so i can load new spreadsheets in at a future date to validate the dates and update them where necessary.
If for instance Visit 1 is completed later than it's due date, then the due date of Visit 2 should change to a new offset based on the space between the visits as determined by the Parent.
e.g.
Parent Code (Series Code) is INSS123456
Parent Code type is 03-01
03-01 has 3 Months between visits
Visit 1 was due on 31/01/22
Visit 1 was completed late on 25/02/22
Visit 2 is originally due on 30/04/22
Visit 2 new due date should now be 25/05/22
Visit 3 new due date should now be 25/08/22
and so on
I'm looking for someone to be able to write some simple code that will identify those visits not done on time (or earlier than the month that they were due in) and revise the due dates accordingly.
This needs to be repeatable so i can load new spreadsheets in at a future date to validate the dates and update them where necessary.