Assistance - Updating the dates are not working

[oliverh1993]

Hey everyone,

I am re-posting my post from yesterday because I left out a lot of the key features and wanted to explain everything clearly.

I have attached an example file to work with and within it has the code and data. I have also attached an image to explain what is expected.

So I am having an issue with the dates not updating correctly and I am not 100% sure it it is caused by leap years or something else.

My goal is to update the existing data with the desired date that has been inputted and have the other dates be updated with the difference between the original date and new date, so for example, if the commencement date = 20/02/2020 and chosen date is 14/02/2023, then the date is updated to it and for the expiry date it is updated to 13/02/2024 (original expiry date being 19/02/2021). Hopefully that makes sense.

Kind regards,

[unit285]

I need a better explanation of what you are trying to accomplish with this code.. these selection loops specifically in the UpdateDates procedure are making me question every decision I have ever made in my life...

[WideBoyDixon]

Days difference between 14/2/2020 and 14/2/2023 = 365*3 + 1 --- the 1 is the 29/2/2020 (since it was a leap year). Adding 1096 days to each of these dates give the values shown in "Outcome". For those dates also starting before 29/2/2020, the extra day will get *consumed* and your outcome is as expected. For dates after this date (e.g. 13/2/2021), it's going to advance it by 3 years and one day to 14/2/2021. If you just want to add a specific number of years to each of the dates, that's easy enough (e.g. adding 3 years to each date). However, if you want to be able to change the day/month as well, the logic is going to get quite tricky.

WBD

[Richard Buttrey]

Welcome to the forum.

Please upload the workbook with the stuff you show in your picture. None of us want to spend time recreating data which you already have. We're then able to quickly test any suggested solution with your manually added dates.
I doubt whether you actually need a macro to do this, and Excel functions will suffice. Even if you need a macro I'd simply get the macro to add the formulae.

Please be concise with your terms. You say 'Desired Date'. I see no mention of that. Are you meaning the Reference date? And to what does 'Chosen date' refer?

When you offer examples of what appear to be wrong results shown in pink then make sure your comment about the example relates to the same wrong results. Because many of your dates are the same it's not easy to see how they relate to the pink examples. Always refer to specific cell references rather than names or dates which apply to many cells.

[WideBoyDixon]

In fairness, there was an attachment in the first post

I re-wrote the code module to be a little tidier:

Please Login or Register  to view this content.

WBD

[Richard Buttrey]

You could do this with formulae.


i.e. after getting the new date in D1 in J4 copied across =$D$1

Then in J7 copied across and down
=IF(ISERROR(FIND("Exp",$D7)),$J$4,J$4-1)

If you wanted to do it with a macro then personally I'd get the macro to temporarily add the formulae to J7:L:whatever then copy J:L and paste back to F7 as values.