Date formatting for several colums

[Bob G.]

First, thanks for any and all help.

I get file dumps from several different PM, with the raw data in different orders. What I have done is, converted the formatting from "dd-mmm-yy hh:mm:ss" to "dd-mmm-yy" removing the "hh:mm:ss", but, now I want to be able to to do it for a whole workbook vs changing the code for each column (output are different from week to week and PM to PM). Also, i very new to VBA coding, so if there is a better way to do something, I am all ears. Note: per company policy I am not able to attach a file.

Sub IsCellBlank_3()
Dim LR As Long, i As Long, XX_1 As Integer, XX_2 As Long, XX_3 As Long, XX_4 As Long, XX_5 As Long, XX_6 As Long
'Dim BX As String

Set sh1 = Sheets("Base")
XX_1 = Application.Match("Start", sh1.Range("1:1"), 0)
XX_2 = Application.Match("Finish", sh1.Range("1:1"), 0)
XX_3 = Application.Match("Actual Start", sh1.Range("1:1"), 0)
XX_4 = Application.Match("Actual Finish", sh1.Range("1:1"), 0)
XX_5 = Application.Match("Late Start", sh1.Range("1:1"), 0)
XX_6 = Application.Match("Late Finish", sh1.Range("1:1"), 0)

For v = 1 To 6
BX = Application.Choose("XX_" & v, "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If IsEmpty(sh1.Range("BX" & i)) Then GoTo ZZ Else GoTo XX
XX: If Range(BX & i) = "" Then GoTo ZZ Else GoTo YY
YY: With Range(BX & i)
    .NumberFormat = "dd-mmm-yy"
    .Value = DateSerial(Year(Range(BX & i)), Month(Range(BX & i)), Day(Range(BX & i)))
    End With
ZZ: Next i
Next v
End Sub

 

[Nebu]

Hi Bob:

Cud u upload a sample file with dummy data in it...

Thanks

 

[Bob G.]

IsCellBlank_3 work for single date but can't get the IsCellBlank_4 to work for a worksheet. See attached sample data...

 

[Bob G.]

Sorry I forgot to changes the tab name to "BASE" to match the code...

 

[NARAYANK991]

Hi Bob ,

See if this is OK.

Narayan

 

[Bob G.]

Thank you. Works like a charm...