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

bobdabuilda

New Member
Joined
Sep 21, 2016
Messages
10
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:

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

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

Code:
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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Have been having more of a look around, and think I may have found a solution - posting here for (hopefully) some feedback to see if this may be the best approach?

What I have in mind, is using the DATEVALUE function to get the "string value" of the dates entered by the user, and store them in the database in that format - and then use cell formatting for user-friendly display. Is this the best approach for dealing with dates of possibly dubious format? Are there any caveats or gotchas of this method that I should be aware of?
 
Upvote 0
Hello Brad,

It isn't clear from your post how the users are entering the date information. I did see mention of a text box "TBox15". Not sure if this on a worksheet or on a VBA UserForm.

It is always best to request the user enter a date in a specific format and then validate a entry before going forward. If the date is not correct then you can ask the user to enter it again or provide them with the option to quit.

I prefer VBA UserForms for several reasons. First, they are displayed modally which prevents the user from switching back to the worksheet until the UserForm is dismissed. Secondly, it provides you with more control over the data entry and output. The downside is it takes more coding than entering data directly to a worksheet.

CDate is internationally aware and will check if a date in is an acceptable format for your locale. The locale settings can be found in the Control Panel. However, it will not, as you discovered, prevent problems arising from ambiguous date formats. This problem arises a lot when U.S. dates and U.K. dates are both used in the same workbook. That is why I suggest you hold the user to one specific format when entering dates.

Entering dates in the format like 28-Dec-16 is short, easy to read, and not ambiguous.
 
Last edited:
Upvote 0
Hi Leith - sorry for not being clear enough in my description. Yes, the dates are being entered via a VBA form. The issue is that, even if the date is entered by the user in the correct format (mm/dd/yyyy), when the data gets copied from the form to the database sheet, the day and month fields will (sometimes) get transposed... seems to depend on whether or not they CAN be, and still be a valid date.
 
Upvote 0
Hello Brad,

It seems to me that the cell format has either been change by the user or not set to a specific date format. You can change the cell format before the date value is copied from the text box. This will ensure the format is always the same. I would do this in the TextBox_AfterUpdate event.
 
Upvote 0
Let me add to Leith's suggestion about the user entering a specific format. Rather than typing in a text box, if the user had to make choices from 3 combo boxes, there would be no confusion on that end.
 
Upvote 0
Hi guys,

Been having a bit more of a play after your suggestions, and some further reading. Mike - thanks for the suggestion, but I will avoid that one for now if I can, as that will require a fair amount of re-coding on my part. I will definitely keep it in mind, though, as it's a reasonably bullet-proof option (although the logic behind February and leap-years may well do my head in).

Leith - I can confirm that the cells the dates are being copied to are formatted in the correct Australian date format. The dates being entered are also being entered in the same format. However... I just did a wee test, where I changed my code to use:

Code:
Int(CDbl(Me.Controls("TBox" & X).Value))
(where TBox & X evaluate to the current Date box on the form)

So that this way, the date is being sent to the spreadsheet in the numeric format of the date value instead of the date value - and then having the formatting of the spreadsheet display it appropriately... or so I thought. I created a new user as a test, put in the date 01/12/2016 (1st December), and the date field in the spreadsheet has transposed the day and the month and are displaying it as 12/01/2016 (changed the display format to include the month name, to be sure - and it IS thinking of it as 12th Jan)... /sigh

Why is it that my VBA is seeing my dates in American date format? My system is configured for Australian dates, Excel is configured the same. What am I missing??
 
Upvote 0
Just one minor thing to add to this - at this point in time, the "user" is my wife - so I know there's nothing untoward being done with regard to changing of formats, or entering dates in the incorrect format, etc. It's just VBA being somewhat ornery, it would seem...

If it would help, I could create a sanitised version of the spreadsheet for you to take a look at, to see what's happening - I could populate it with a couple of test records with dates broken and not broken. It just really IS acting rather strangely and doing my head in - especially with it being seemingly intermittent and inconsistent...
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top