VBA Convert String Date to Date Value

VBA formula to convert a Text date to Date format.  Convert string date to date format.
CDate part of the formula converts the string to a date.
Format part of the formula lets you decide what format the date should be in.

datestr = Format(CDate(Cells(1, 1).Value), "dd/mm/yyyy")
datestr2 = Format(CDate(Range("A1").Value), "dd/mm/yyyy")
datestr3 = Format(CDate("2018-01-10"), "dd/mm/yyyy")

DateTime formats

Character Description Format Argument Expression Argument Formatted Display
/ (forward slash) Date separator. Separates the day, month & year while formatting date values. mm/dd/yyyy 41463 07/08/2013
c Date is displayed as ddddd and time is displayed as ttttt, in that order. Only date is displayed if date serial number does not have a fractional part and only time information is displayed if no integer portion. c 41463.251 7/8/2013 6:01:26 AM
  c 0.251 6:01:26 AM
  c 41463 7/8/2013
d Day is displayed as a number, as one digit or as two digit, without a leading zero. (1-31) mm/d/yyyy 41463 07/8/2013
dd Day is displayed as a number, as two digit, with a leading zero where applicable. (01-31) mm/dd/yyyy 41463 07/08/2013
ddd Day is abbreviated to three letters, viz. Sunday is displayed as Sun. (Sun-Sat) ddd, mmm d, yyyy 41463 Mon, Jul 8, 2013
dddd Day is displayed in its full format, viz. Sunday is displayed as Sunday. (Sunday-Saturday) dddd, mmm d, yyyy 41463 Monday, Jul 8, 2013
ddddd Display a date serial number as a complete date (including day, month, and year) formatted according to the short date setting recognized by your system. The default short date format is m/d/yy. ddddd 41463 7/8/2013 (Short Date Format in the system showing the display is: dddd, m/d/yyyy)
dddddd Display a date serial number as a complete date (including day, month, and year) formatted according to the long date setting recognized by your system. The default long date format is mmmm dd, yyyy. dddddd 41463 Monday, July 8, 2013  (Long Date Format in the system showing the display is: dddd, MMMM d, yyyy)
aaaa Displays the full, localized name of the day in its full format (same as dddd, except that its localized version).
w Day of the week is displayed as a number (1-7 for Sunday-Saturday). w, mmm d, yyyy  41463 2, Jul 8, 2013
ww  Week of the year is displayed as a number. (1 to 54) ww #7/8/2013# 28
m Month is displayed as a number, as one digit or as two digit, without a leading zero – (1-12).  To use m as minute(s), it should appear immediately after the h or hh code, such as “h:m”. m/d/yyyy 41463 7/8/2013
mm Month is displayed as a number, as two digit, with a leading zero where applicable – (01-12). To use mm as minute(s), it should appear immediately after the h or hh code, such as “h:mm”. mm/d/yyyy 41463 07/8/2013
mmm Month name is abbreviated to three letters, viz. January is displayed as Jan. (Jan-Dec) mmm d, yyyy 41463 Jul 8, 2013
mmmm Month is displayed in its full name, viz. January is displayed as January. (January-December) mmmm d, yyyy 41463 July 8, 2013
oooo Displays the full localized name of the month (same as mmmm, except that its localized version).
q Quarter of the year is displayed as a number. (1 to 4) q #7/8/2013# 3
y Day of the year is displayed as a number. (1 to 366) y #7/8/2013# 189
yy Year is displayed as a number in two digits, viz. last 2 digits of the year are displayed. (00-99) m/d/yy 41463 7/8/13
yyyy Year is displayed as a number in four digits, viz. all digits of the year are displayed. (1900-9999) m/d/yyyy 41463 7/8/2013
 
Time separator. This separates hours, minutes & seconds while formatting time values. h:n:ss AMPM 0.251 6:1:26 AM
h Hour is displayed as a number, as one digit or as two digit, without leading zeros. (0-23) h:nn:ss AMPM 0.251 6:01:26 AM
Hh Hour is displayed as a number, as two digit, with a leading zero where applicable. (00-23) Hh:n:ss AMPM 0.251 06:1:26 AM
N Minute is displayed as a number, as one digit or as two digit, without leading zeros –  (0 to 59). You can also use m code as minute (m is used for displaying month), for which it should appear immediately after the h or hh code, such as “h:m”. n:ss 0.251 1:26
Nn Minute is displayed as a number, as two digit, with a leading zero where applicable –  (00 to 59). You can also use m code as minute (m is used for displaying month), for which it should appear immediately after the h or hh code, such as “h:m”. Nn:s 0.251 01:26
S Second is displayed as a number, as one digit or as two digit, without leading zeros. (0 – 59) h:n:S 0.2591 6:13:6
Ss Second is displayed as a number, as two digit, with a leading zero where applicable.  (00 – 59) h:n:Ss 0.2591 6:13:06
t t t t t   Complete time is displayed (including hour, minute, and second) wherein the time separator, as defined by the time format recognized by your system, is used. If the leading zero option is selected and the time is before 10:00 AM / PM, a leading zero will be displayed. h:mm:ss is the default format. ttttt 0.25631 6:09:05 AM
AM/PM, am/pm, A/P, a/p or AMPM If these codes are included in the format, the hour is displayed using a 12-hour clock, else the hour is based on the 24-hour format. Display will include AM, am, A or a for a time before noon, and PM, pm, P or p for a time ‘from’ and ‘after’ noon till 11:59 PM. While using AMPM, the case can be UPPER or lower, matching the string as defined by your system settings. AM/PM is the Default format. Hh:n:ss A/P 0.251 06:1:26 A

Date time format keywords

'returns "7/31/2014 8:52:48 PM"
str = Format(SerialNo, "General Date")
MsgBox str

'returns "Thursday, July 31, 2014"
str = Format(MyDate, "Long Date")
MsgBox str

'returns "31-July-14"
str = Format(MyDate, "Medium Date")
MsgBox str

'returns "7/31/2014"
str = Format(MyDate, "Short Date")
MsgBox str

'returns "7:16:32 PM"
str = Format(MyTime, "Long Time")
MsgBox str

'returns "07:16 PM"
str = Format(MyTime, "Medium Time")
MsgBox str

'returns "19:16"
str = Format(MyTime, "Short Time")
MsgBox str