Hijri Date Formatting-CodePudding

I have some data that is downloaded from a website, one column of this data is containing Hijri date. In order to have this column as proper Date column I applied the below formatting:

but the issue it will not be considered as date and be aligned to the Right unless I enter the cell (by double click or F2) and then press Enter

Because the number of rows is big the way I'm using is not practical

I tried the following:

Copy the cells to Notepad and then paste again in excel but didn't work
Replaced the date separator from "-" to "." and then replace back to "-" as if the date separator was "." and replaced by "-" that usually working for the Gregorian date converting it from Text to Date
Created a VBA code applying the code for the selected cells to enter the cells and exit them to refresh, check below:

Sub HijriDateEnforce()
  Dim cel As Range
  Dim selectedRange As Range
  Set selectedRange = Application.Selection
  For Each cel In selectedRange.Cells
    Selection.NumberFormat = "[$-1970000]B2dd/mm/yyyy;@"
    SendKeys "{F2}~"
  Next cel
End Sub

but the code for unknown reason if the number of rows is very big it stops after some time and I have to run it again for the remaining cells

CodePudding user response：

I hate suggesting .Select but what I suggested earlier doesn't work in your scenario. So try this

Code

Sub HijriDateEnforce()
    Dim acell As Range
    Dim selectedRange As Range
    
    Set selectedRange = Application.Selection
    selectedRange.NumberFormat = "[$-1970000]B2dd/mm/yyyy;@"
    
    For Each acell In selectedRange
        acell.Select
        Application.SendKeys "{F2}"
        Application.SendKeys "{ENTER}"
        DoEvents
    Next acell
End Sub

In Action