How to Convert Text Dates to Date format

[NeoPa]

This thread is a response to a very popular thread (How to convert Text dates to Date format?) which is locked down now so I thought I'd reply separately. This should be considered as a continuation of the topic where, hopefully, I can clarify any confusion.

Always bear in mind that date values and date literals are completely different animals. Literal DateTimes and Their Delimiters (#) may help with the understanding.

A date value, with or without the date or time portions, is stored internally (In most systems.) as a double-precision count of the number of days past a certain point in history. Whenever you see a date/time value displayed it is almost certainly after the value has been formatted. It's really very important to understand this point.

A date literal, as you may find in a value used within SQL for instance, will be a formatted string. As you'll see from the linked article above the formatting can vary considerably - even within SQL - but it's always a string.

Most of the problems I've seen questions about can all boil down to the simple confusion of the person with the problem treating something as one type (Double or String) when they really should have treated it as the other. IE. Get that right in your head and you're very unlikely to experience problems.

From a date value (Double) you can select one part and exclude the other by using two of the VBA functions (The library VBA as opposed to the language.) :

DateValue()
TimeValue()

These each return just the part specified in the name.

[svtsolutionllc]

You can convert text date to date format by using following function procedures.

DateValue()
TimeValue()

[NeoPa]

Why would you send a reply that is only a small part of the answer when the question has already been dealt with in full?

I take time to read what's been added but nothing whatsoever has been added. Just a small part copied - and even that had to be corrected.

[LeoDEL]

Using the DATEVALUE function is a basic method to convert a date into an actual date which is stored as a text.

[NeoPa]

Hi Leo.

While you haven't added anything to the discussion that wasn't already there and properly described in the original article, you have managed to introduce some confusion in what you've posted. Let me see if I can clarify that so that nobody gets confused. Generally speaking of course, it's better to read the original before replying and only reply if you have something to add that may be helpful.

While code that uses the DateValue() function in VBA will return a date value if a string value is passed, it isn't the fact of calling the function which ensures the string value is converted to a date value. It does have input in as much as the parameter expected being specified as Date/Time ensures the compiler will recognise that the value expected is a Date/Time (Technically a Date type but in VBA this handles both dates & times of course.) and therefore do a conversion for you before the value is passed to the function.

Essentially the DateValue() function will be passed a value that has already been converted to Date/Time if the value passed is capable of being interpreted that way. It will not even have any way of determining that the original value was different from what it was passed.

VBA does a lot of conversions for you automatically. It's a concept of the language.