Differences

This shows you the differences between two versions of the page.

--- microsoft_excel:macros:change_format_of_date [2021/08/04 14:00] – created peter
+++ microsoft_excel:macros:change_format_of_date [2021/08/04 14:56] (current) – removed peter
@@ Line 1: / Line 1: @@
-====== Microsoft Excel - Macros - Change format of 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>