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

CharacterDescriptionFormat ArgumentExpression ArgumentFormatted Display
/ (forward slash)Date separator. Separates the day, month & year while formatting date values.mm/dd/yyyy4146307/08/2013
cDate 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.c41463.2517/8/2013 6:01:26 AM
 c0.2516:01:26 AM
 c414637/8/2013
dDay is displayed as a number, as one digit or as two digit, without a leading zero. (1-31)mm/d/yyyy4146307/8/2013
ddDay is displayed as a number, as two digit, with a leading zero where applicable. (01-31)mm/dd/yyyy4146307/08/2013
dddDay is abbreviated to three letters, viz. Sunday is displayed as Sun. (Sun-Sat)ddd, mmm d, yyyy41463Mon, Jul 8, 2013
ddddDay is displayed in its full format, viz. Sunday is displayed as Sunday. (Sunday-Saturday)dddd, mmm d, yyyy41463Monday, Jul 8, 2013
dddddDisplay 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.ddddd414637/8/2013 (Short Date Format in the system showing the display is: dddd, m/d/yyyy)
ddddddDisplay 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.dddddd41463Monday, July 8, 2013  (Long Date Format in the system showing the display is: dddd, MMMM d, yyyy)
aaaaDisplays the full, localized name of the day in its full format (same as dddd, except that its localized version).
wDay of the week is displayed as a number (1-7 for Sunday-Saturday).w, mmm d, yyyy 414632, Jul 8, 2013
ww Week of the year is displayed as a number. (1 to 54)ww#7/8/2013#28
mMonth 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/yyyy414637/8/2013
mmMonth 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/yyyy4146307/8/2013
mmmMonth name is abbreviated to three letters, viz. January is displayed as Jan. (Jan-Dec)mmm d, yyyy41463Jul 8, 2013
mmmmMonth is displayed in its full name, viz. January is displayed as January. (January-December)mmmm d, yyyy41463July 8, 2013
ooooDisplays the full localized name of the month (same as mmmm, except that its localized version).
qQuarter of the year is displayed as a number. (1 to 4)q#7/8/2013#3
yDay of the year is displayed as a number. (1 to 366)y#7/8/2013#189
yyYear is displayed as a number in two digits, viz. last 2 digits of the year are displayed. (00-99)m/d/yy414637/8/13
yyyyYear is displayed as a number in four digits, viz. all digits of the year are displayed. (1900-9999)m/d/yyyy414637/8/2013
 
Time separator. This separates hours, minutes & seconds while formatting time values.h:n:ss AMPM0.2516:1:26 AM
hHour is displayed as a number, as one digit or as two digit, without leading zeros. (0-23)h:nn:ss AMPM0.2516:01:26 AM
HhHour is displayed as a number, as two digit, with a leading zero where applicable. (00-23)Hh:n:ss AMPM0.25106:1:26 AM
NMinute 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:ss0.2511:26
NnMinute 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:s0.25101:26
SSecond is displayed as a number, as one digit or as two digit, without leading zeros. (0 – 59)h:n:S0.25916:13:6
SsSecond is displayed as a number, as two digit, with a leading zero where applicable.  (00 – 59)h:n:Ss0.25916: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.ttttt0.256316:09:05 AM
AM/PM, am/pm, A/P, a/p or AMPMIf 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/P0.25106: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