Excel macro vba script

Question

I am trying to format a column in excel sheet where regular format datetime to date option is not working as I exported data in excel from clear quest. I need date column in mm/dd/yyyy format bis there a VB script that can do this.

duplicate: http://stackoverflow.com/q/19801598/119775 – Jean-François Corbett Apr 19 '15 at 20:18 — Jean-François Corbett, Apr 19 '15 at 20:18

Three-D · Answer 1 · 2015-04-08T19:00:37.930

0

Sub DateFormat()
     Range("C2:C64").NumberFormat = "mm/dd/yyyy"
End Sub

edited Apr 08 '15 at 19:00

answered Apr 08 '15 at 14:52

Three-D

75
1
8

If it's for only one column C range C2:C64 how will the code be ? – user803860 Apr 08 '15 at 15:02
@user803860 - Change "Columns("G:G").Select" to "Range("C2:C64").Select" – Three-D Apr 08 '15 at 15:06
Sub DateFormat() ' 'Change G:G to the column(s) you would like to change ' Columns("C2:C64").Select Selection.NumberFormat = "mm/dd/yyyy" Range("C2").Select End Sub – user803860 Apr 08 '15 at 17:49
@user803860 - you have to change "Columns" to "Range".......or, just use the new code after I did the edit to my answer. – Three-D Apr 08 '15 at 19:01
@user803860 - did this do what you needed? if so, please mark as answered. if not, please let me know what you are seeing. thanks :) – Three-D Apr 09 '15 at 12:21
That didn't work seems like when you export from clear quest it doesn't let u format – user803860 Apr 09 '15 at 13:04

score 0 · Answer 2 · edited May 23 '17 at 12:28

0

Based on this post: Excel VBA date formats

Function CellContentCanBeInterpretedAsADate(cell As Range) As Boolean
    Dim d As Date
    On Error Resume Next
    d = CDate(cell.Value)
    If Err.Number <> 0 Then
        CellContentCanBeInterpretedAsADate = False
    Else
        CellContentCanBeInterpretedAsADate = True
    End If
    On Error GoTo 0
End Function

edited May 23 '17 at 12:28

Community

1
1

answered Apr 08 '15 at 14:54

Hugues Paquet Blanchette

612
3
11
24

Excel macro vba script

2 Answers2