Excel Guides

Converting Mainframe Date Formats in Excel

There are a few different ways to convert Mainframe date formats in Excel. One way is to use the Text to Columns feature. To do this, first select the column of data that you want to convert. Then, go to the Data tab on the ribbon and click on the Text to Columns button. In the Convert Text to Columns Wizard, select Delimited as the file type and click Next. On the next page, select Other as the delimiter and enter a backslash (\). Then, click Finish.

Another way to convert Mainframe date formats in Excel is to use a formula. For example, if your data is in column A and you want to convert it to the dd/mm/yyyy format, you can use the following formula:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

This formula will extract the year from the left side of the cell, the month from the middle of the cell, and the day from the right side of the cell. It will then put those values into the DATE function, which will output a date in dd/mm/yyyy format.

If you have a lot of data to convert, you can use a macro. The following macro will convert all dates in column A to dd/mm/yyyy format:

Sub ConvertDates()

    Dim i As Long

    For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row

        Cells(i, "A") = DateValue(Cells(i, "A"))

    Next i
    
End Sub

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.