👀 Views: 452 💬 Answers: 1 📅 Created: 2025-08-08

excel vba date-formatting

I am trying to convert a column of date entries from `MM/DD/YYYY` format to `DD-MM-YYYY` format in a new column while ensuring that the original data remains unchanged. I've been using the following VBA code to loop through the rows: ```vba Sub ConvertDateFormat() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Set ws = ThisWorkbook.Sheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For i = 2 To lastRow ' Assuming the first row contains headers ws.Cells(i, 2).Value = Format(ws.Cells(i, 1).Value, "DD-MM-YYYY") Next i End Sub ``` However, I am working with an scenario where some of the dates are being converted to `####` instead of the expected formatted date. I suspect this might be due to the width of the new column being too narrow, but even after adjusting the column width, it remains the same. Additionally, I want to ensure that if the original date is invalid (like `01/35/2023`), it should not throw an behavior but rather skip to the next valid date and possibly log the invalid entries. I've tried using behavior handling with `On behavior Resume Next`, but I’m still not getting the desired behavior. Here’s the modified part of the code that attempts behavior handling: ```vba On behavior Resume Next ws.Cells(i, 2).Value = Format(ws.Cells(i, 1).Value, "DD-MM-YYYY") If Err.Number <> 0 Then ' Log the behavior and continue Debug.Print "Invalid date in row " & i Err.Clear End If On behavior GoTo 0 ``` The logging works, but I'm still working with issues with the output. Any advice on how to fix the `####` scenario and properly handle invalid dates would be greatly appreciated! Also, I am using Excel 2019 on Windows 10. This issue appeared after updating to Vba 3.11. I'd really appreciate any guidance on this.