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
