Formatting date columns as text using VBA - Question

Question

The problem: the dates displayed in my excel files are not the actual dates entered into each cell. For example, I might visually see 04/18/2019 but when I click on the cell, it shows 04/18/2019 06:48:00PM. This becomes an issue when I run the macro I use to convert my xlsx file to txt. The txt file generated will show 04/19/2019 06:48:00PM in the date field rather than what I want, 04/18/2019.

The current fix I'm using is to insert columns next to the date columns, copy the date columns and paste values into the new inserted columns, and then deleting the inserted columns. This was accomplished through recording a macro (I'm a noob if you couldn't tell).

That rudimentary fix does actually work quite well at generating the output I need. Except one problem: if there are any blanks in the date columns, it pastes in 01/01/1900. Blanks in date columns is fairly common in the work I do so this is a major problem.

I tried adding this If in after the inserting/pasting values/deleting code:

'Fix Date format in columns A and C

'

Columns("B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""mm"/"dd"/"yyyy"")"
Range("E1").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""mm"/"dd"/"yyyy"")"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B" & EndRow)
Range("E1").Select
Selection.AutoFill Destination:=Range("E1:E" & EndRow)
Range("B1:B" & EndRow).Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("E1:E" & EndRow).Select
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Columns("E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

If Range("A1:A") = "01/01/1900" Then ClearContents

'

But I get this error "Compile Error: Sub or Function not defined", with the word Range within the If statement highlighted. I don't understand why it isn't recognizing range since it is used just a few lines up without issue. I have to be doing something wrong but I'm lost. Any ideas?

PS - The If is just a means to an end as far as remedying the blanks, but I'm sure there's a better way to convert these date columns to text or to the date format I want. If you have any recommendations for some easier way to achieve the same results I am all ears.

Thanks guys I really appreciate it!

score 0 · Answer 1 · 2019-04-19T00:24:30.787

Range("A1:A")

That's a google-sheets reference for 'everything from A1 down to the last used cell in column A' not a VBA style. Either reference the entire column (e.g. Range("A:A")) or modify the end row with your EndRow var (e.g. Range("A1:A" & EndRow)).
The formating mask used in the VBA application of the TEXT formula seems wrong. When using a quote within a quoted string,double up the quote once every time it ix used. The TEXT formula returns a string (i.e. text); you do not need to attempt any further conversion.
```
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""mm/dd/yyyy"")"
```
Both xlR1C1 and relative styled xlA1 formulas be applied to all cells at once. A Union of the target cells in columns B and E further expedites the formula insert.
```
Range("B1:B" & EndRow & ",E1:E" & EndRow).formular1c1 = "=TEXT(RC[-1],""mm/dd/yyyy"")"
```
You cannot directly compare a multiple cell range to a single value. Either loop through the cells or use a wide-range comparison like Range.Find, the worksheet's MATCH function or an AutoFilter to expose the matching cells as xlCellTypeVisible.
```
dim m as variant

m = application.match("01/01/1900", ActiveSheet.Range("A:A"), 0)

do while not iserror(m)
    ActiveSheet.Cells(m, "A").clearcontents
    m = application.match("01/01/1900", ActiveSheet.Range("A:A"), 0)
loop
```

If you've read down this far, the easiest method of stripping the date out of a date-time is Text-to-Columns. Simply discard anything after the 10th character (e.g. mm/dd/yyyy from mm/dd/yyyy hh:mm amp/pm).

With ActiveSheet.Columns("A")
    .TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, _
                   FieldInfo:=Array(Array(0, 1), Array(10, 9))
    .NumberFormat = "mm/dd/yyyy"
End With

I would humbly suggest you take some time to investigate How to avoid using Select in Excel VBA. If you continue to develop VBA to make your Office applications more efficient, you should start using recommended methods as soon as possible.

Formatting date columns as text using VBA - Question

1 Answers1