Hijra Date into Gregorian

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Welcome to the board!

1. On the Format menu, click Cells, and then click the Number tab.
2. Select Custom from the list of categories.
3. In the Type box, do one of the following:

Gregorian Format - "B1dd/mm/yy" - To display dates using the Gregorian calendar, regardless of the Regional Options or Regional Settings of the Microsoft Windows Control Panel setting, type B1 before the date format.

Hijri Format - "B2dd/mm/yy" – to type the date in Gregorian format but it will be interpreted and displayed as Hijri date.

Hope that helps.
 
Upvote 0
1st, thanks a lot for your quick reply but i tried that already but no useful :(

the excel sheet that i have was made by Excel 2003 and I tried to load it using 2007 & 2010 but still it displayed the same Hihra dates

29/08/1432 for 30-07-2011
29/08/1432 for 29-04-2009

i have around 10 columns contain more than 1000 dates that i need to convert
i tried change the date format in these columns but it keep display the dates in Hijra
 
Upvote 0
Are you sure that your dates are serial dates, not text entries that look like dates? You can use the ISNUMBER function to check - it will return TRUE if the cell contains a serial date.
 
Upvote 0
Thanks Andrew
however, it works for Cell by Cell but how i can populate it to all cells !! or I have to update all cells one at a time !!
 
Upvote 0
Sorry, I don't know how to convert all cells at once without a macro. This worked for me:

Code:
Sub Test()
    Dim Rng As Range
    Dim Cell As Range
    Set Rng = Range("A1").SpecialCells(xlCellTypeConstants, 2)
    For Each Cell In Rng.Cells
        If Cell.Value Like "##/##/####" Then
            SendKeys "{F2}{HOME}a{ENTER}"
            Cell.Select
            DoEvents
        End If
    Next Cell
End Sub
 
Upvote 0
Thank you very much Andrew
I works fine

by the way, can it be written as a function so i can call from any cell instead of run a macro that change the whole cells ?

it might be more helpful if I want to keep both dates in the sheet
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top