VBA - How best to handle dates so format is changed/lost?

[bobdabuilda]

Hi guys,

I'm working on a "membership" database for a greyhound rescue volunteer group, to try and help them organise things a bit better (their "manual" system wasn't too flash). I've got things working (for the most part) how I want them to... just fixing minor issues as they come up, now that they've started using it.

One thing that HAS come back up again, though, is dates changing their format. I thought I had it sorted by using the CDate function on the data entered to convert it to a date before storing it:

CDate(Sheet5.Cells(lastrow, 17).Value)

I also have a Format command in use to make sure things look OK:

Format(CDate(TBox15.Value), "dd/mm/yyyy")

But I've found that dates which can be interpreted differently sometimes are eg. 10/8/2016 (10th August) will be saved to the spreadsheet as 08/10/2016 (8th October). Considering we're talking about paid annual memberships, we need to have the dates right.

I've been looking around trying to find VBA coding best practices for dates, but haven't been able to find anything overly conclusive. Hoping someone can perhaps point me in the right direction so I can stop these folks from having to edit things manually when they find issues? They're REALLY not the kind that I want to leave poking around "under the bonnet" too often... I'll just end up with bigger problems (or too many phone calls lol).

(Hope you're all having a great Xmas break )

Cheers,
Brad.

 

[bobdabuilda]

Well, I didn't THINK it was a bother... but I set up a sanitised version of the spreadsheet, entered in some more members in order to recreate the issue with some data in there to illustrate the problem... and I couldn't get the problem to re-occur... the joys of dealing with intermittent issues...

I think that, at this point in time, I need to thank you all for your assistance so far, and then shelve the issue until such time as it comes up again and I have a better chance of replicating the problem, so I can ask a better, more informed request for assistance next time. Very frustrating...

(Doesn't help that the wife can't remember which dates it was, which members she'd entered which were wrong (she fixed them manually grrr), or anything else even remotely helpful...)