Convert number in yyyymmdd order to date
To convert number in yyyymmdd order to date you need to apply a combination of the Excel DATE, LEFT, MID and RIGHT functions
Example: Convert number in yyyymmdd order to date
Sub Convert_number_in_yyyymmdd_order_to_date()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to convert the number in cell ("B5"), which is represented in yyyymmdd order to date
ws.Range("E5") = DateSerial(Left(ws.Range("B5"), 4), Mid(ws.Range("B5"), 5, 2), Right(ws.Range("B5"), 2))
ws.Range("E5") = DateSerial(Left(ws.Range("B5"), 4), Mid(ws.Range("B5"), 5, 2), Right(ws.Range("B5"), 2))
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analyst.
Number to convert to Date: Ensure that the number that you want to convert to date is captured in cell ("B5") and is in the yyyymmdd order.
Worksheet Name: Have a worksheet named Analyst.
Number to convert to Date: Ensure that the number that you want to convert to date is captured in cell ("B5") and is in the yyyymmdd order.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Number to convert to Date: Select the number that you want to convert to date by changing the cell reference ("B5") to any cell in the worksheet that contains the number that you want to convert to date and doesn't conflict with the formula.
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Number to convert to Date: Select the number that you want to convert to date by changing the cell reference ("B5") to any cell in the worksheet that contains the number that you want to convert to date and doesn't conflict with the formula.
EXPLANATION
To convert number in yyyymmdd order to date you need to apply a combination of the Excel DATE, LEFT, MID and RIGHT functions.
To convert number in yyyymmdd order to date you need to apply a combination of the Excel DATE, LEFT, MID and RIGHT functions.
FORMULA
=DATE(LEFT(number,4),MID(number,5,2),RIGHT(number,2))
=DATE(LEFT(number,4),MID(number,5,2),RIGHT(number,2))
ARGUMENTS
number: A numeric value in yyyymmdd order to to convert to a date format.