Hijri Date Formatting

Question

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

It is a date formatted column aligned to the Left side of the cell as text and after entering the cell and then pressing Enter it will be aligned to the Right as Date I hope my explanation is clear — Abdullah Bayounis, Nov 18 '21 at 08:29
if you mean the dates in the cells they are like: 01-01-1444 — Abdullah Bayounis, Nov 18 '21 at 08:43

Siddharth Rout · Accepted Answer · 2021-11-18T10:15:36.550

2

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

edited Nov 18 '21 at 10:15

answered Nov 18 '21 at 07:36

Siddharth Rout

147,039
17
206
250

Thanks for your response, but unfortunately it is still the same I have to enter each cell to get the cell formatted as Hijri Date – Abdullah Bayounis Nov 18 '21 at 08:04
I have updated the code so try it now. – Siddharth Rout Nov 18 '21 at 10:09
The code is working perfectly and not stopping till the end. Thanks a lot for you time. – Abdullah Bayounis Nov 18 '21 at 11:17
Do you know any way to do that without VBA? as the xlsm (Macro Enabled files) files are not allowed due to IT dept. policy – Abdullah Bayounis Nov 18 '21 at 11:20

Hijri Date Formatting

1 Answers1

Linked