Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

====== Microsoft Excel - Macros - Format - Format Date ======

<code excel>

Dim lastrow_sheet123 As Long


' Get how many rows of data have been loaded into the sheet.
lastrow_sheet123 = .Cells(Rows.Count, 4).End(xlUp).Row
' Prevent line 2 being deleted - as this contains the formulae which need coping down later.
If lastrow_sheet123 < 3 Then
  lastrow_sheet123 = 3
End If


' Convert the date using a formula.
' In order to convert the date it is necessary to use a formula that copies the data from the input file instead of copy/pasting it.
.Range("C2").Formula = "=if('DIVI - Auto'!J2>9999999,DATE(RIGHT('Sheet123'!J2,4),MID('Sheet123'!J2,3,2),LEFT('Sheet123'!J2,2)),DATE(RIGHT('Sheet123'!J2,4),MID('DIVI - Auto'!J2,2,2),LEFT('Sheet123'!J2,1)))"

            
            
' Change format of date.

.Range("C2:C" & lastrow_sheet123).NumberFormat = "yyyymmdd"
.Range("C2:C" & lastrow_sheet123).Calculate
</code>