Identification and Reformatting of Dates

[reberryjr]

I'm in need of some assistance with some data calculations. I ingest data from 24 files. Most of the files have a date that I bring over into column D, but they aren't all formatted the same. Examples of the ingested formatting are:
• Serial Number
• YYYY-MM
• YYYY.MM.DD
• Blank
I'm trying to interrogate the value in column D, and populate column E with the date formatted as "MMM-YY". I'm sure there's a more efficient route than the one I was going down, but here's what I've tried unsuccessfully. I only got about 90 minutes of sleep last night, so maybe that's why I'm extra dumb today.

Sub FormatTime()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook
Dim mD As Worksheet
Dim c As Range, rng As Range
Dim mDLR As Long

Set m = ThisWorkbook
Set mD = m.Sheets("Data")

Set rng = mD.Range("D2", mD.Range("D" & mD.Rows.Count).End(xlUp))

mDLR = mD.Range("C" & Rows.Count).End(xlUp).Row

For Each c In rng
    If c.Value = "" Then
        c.Offset(, 1).Value = "N/A"
    Else
        c.Offset(, 1).Value = "=TEXT(RC[-1],""MMM-YY"")"
    End If
Next c

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Bosquedeguate]

Will this work for you? ...
Use the Replace function to get your date into a format that you can use, e.g.
?Replace("2023.07.13",".","-") 'replaces the "." with "-"
2023-07-13
?Replace("2023-07-13",".","-") 'test to make sure Replace would not affect a string like "2023-07-13"
2023-07-13
Format(Datevalue("2023-07-13"),"MMM-YY") 'Use the format function to format your date
Jul-23

 

[reberryjr]

Will this work for you? ...
Use the Replace function to get your date into a format that you can use, e.g.
?Replace("2023.07.13",".","-") 'replaces the "." with "-"
2023-07-13
?Replace("2023-07-13",".","-") 'test to make sure Replace would not affect a string like "2023-07-13"
2023-07-13
Format(Datevalue("2023-07-13"),"MMM-YY") 'Use the format function to format your date
Jul-23

I think my issue is more along the lines of looping through each cell, interrogating the contents, taking action and moving onto the next cell.

 

[MARK858]

If you place the formula below in row 2 of a blank column and drag down which of the various values (I won't call them formats) you have in post one gives FALSE as a result?

=ISNUMBER(D2)

 

[Bosquedeguate]

Date	MMM-YY
7/13/2023	Jul-23
7/11/2023	Jul-23
2023.07.09	Jul-23

Here is the VBA Code for the Macro for Column B

Sub FormatDates()
Dim sheet As Worksheet
Dim rng As Range
Dim r As Integer
Dim dtStr As String

Dim dt As Date


Set sheet = Worksheets("Sheet1")

For r = 2 To 4
dtStr = Replace(sheet.Range("A" & r), ".", "-")

dt = DateValue(dtStr)

With sheet.Range("B" & r)
.Value = dt
.NumberFormat = "MMM-YY"
End With

Next r

End Sub

 

[Bosquedeguate]

Date	MMM-YY
7/13/2023	Jul-23
7/11/2023	Jul-23
2023.07.09	Jul-23

Here is the VBA Code for the Macro for Column B

Sub FormatDates()
Dim sheet As Worksheet
Dim rng As Range
Dim r As Integer
Dim dtStr As String

Dim dt As Date


Set sheet = Worksheets("Sheet1")

For r = 2 To 4
dtStr = Replace(sheet.Range("A" & r), ".", "-")

dt = DateValue(dtStr)

With sheet.Range("B" & r)
.Value = dt
.NumberFormat = "MMM-YY"
End With

Next r

End Sub

Mini-sheet change

DateFormat.xlsm
	A	B
1	Date	MMM-YY
2	7/13/2023	Jul-23
3	2023-07-11	Jul-23
4	2023.07.09	Jul-23
Sheet1

 

[reberryjr]

@Bosquedeguate Thanks for the responses. I'm getting a Type Mismatch at

dt = DateValue(dtStr)

@MARK858 Items arleady formatted as MM/DD/YYYY, YYYY.MM.DD and YYYY-MM are resulting in a FALSE

 

[Bosquedeguate]

Hmmm - I am testing this with Excel 2016 on a desktop and not getting the Type Mismatch error

Try this version of the macro. I added a test for Date Type and do not do date conversion. Let me know if this works (or does not work).

Sub FormatDates()
  Dim sheet As Worksheet
  Dim rng As Range
  Dim r As Integer
  Dim dtstr As String
  
  Dim dt As Date
  
  
  Set sheet = Worksheets("Sheet1")
  For r = 2 To 7
    If VarType(sheet.Range("A" & r)) = vbDate Then
      dt = sheet.Range("A" & r)
    Else
      dtstr = Replace(sheet.Range("A" & r), ".", "-")
      dt = DateValue(dtstr)
    End If
    
    With sheet.Range("B" & r)
      .Value = dt
      .NumberFormat = "MMM-YY"
    End With
    
  Next r
    
End Sub

 

[reberryjr]

Hmmm - I am testing this with Excel 2016 on a desktop and not getting the Type Mismatch error

Try this version of the macro. I added a test for Date Type and do not do date conversion. Let me know if this works (or does not work).

Sub FormatDates()
  Dim sheet As Worksheet
  Dim rng As Range
  Dim r As Integer
  Dim dtstr As String
 
  Dim dt As Date
 
 
  Set sheet = Worksheets("Sheet1")
  For r = 2 To 7
    If VarType(sheet.Range("A" & r)) = vbDate Then
      dt = sheet.Range("A" & r)
    Else
      dtstr = Replace(sheet.Range("A" & r), ".", "-")
      dt = DateValue(dtstr)
    End If
   
    With sheet.Range("B" & r)
      .Value = dt
      .NumberFormat = "MMM-YY"
    End With
   
  Next r
   
End Sub

Here's what I'm using. I'm getting an application defined or object defined error (location is commented out)

Sub DateCalcs1()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook
Dim mD As Worksheet
Dim mDLR As Long
Dim rng As Range
Dim r As Integer
Dim dsStr As String
Dim dt As Date

Set m = ThisWorkbook
Set mD = m.Sheets("Data")

mDLR = mD.Range("A" & Rows.Count).End(xlUp).Row

'For r = 2 To mDLR
'    dtstr = Replace(mD.Range("D" & r), ".", "-")
'
'    dt = DateValue(dtstr)
'
'    With mD.Range("E" & r)
'        .Value = dt
'        .NumberFormat = "MMM-YY"
'    End With
'Next r

For r = 2 To mDLR
    If VarType(mD.Range("D" & r)) = vbDate Then
        dt = mD.Range("D" & r)
    Else
        dtstr = Replace(mD.Range("D" & r), ".", "-")
        dt = DateValue(dtstr)
    End If
    
    With mD.Range("E" & r)
        .Value = dt 'Application defined or Object defined error
        .NumberFormat = "MMM-YY"
    End With
Next r

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Bosquedeguate]

do you have VBA debugging capability? If you do set a break-point in the top of your loop and step thru the code. Find where the error is occuring (at what line of code).