Hijri Dates to Gregorian Excel

I've got some data would take me hours to standardize if I go through it one by one.

01-06-1438 01-06-1439
1-6-1436 30-5-1437 01-6-1437 01-06-1438

Now I need something that can do this on the fly and push out something like this

01-06-1438 01-06-1439 28-02-2017 17-02-2018

Thanks and Best Regards

Edit: I'm using a user defined module to sort this one out, Its working nicely so far.

2 Answers

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.

Say the value in A1 displays as:

01-06-1438

If this is a genuine Excel date, then just changing the format will convert it to Gregorian.

However, if changing the format does not change the cell display, then the data is Text.

It is very easy to convert the Hijri text-date into a Gregorian true date:

In K1 enter:

=TEXT(ROW(),"B2dd-mm-yyyy")

and in L1 enter:

=ROW()

Format L1 as dd-mm-yyyy. Then copy K1 and L1 down through row 1000000

Now we have a VLOOKUP table. In B1 enter:

=VLOOKUP(A1,K:L,2,FALSE)

and format B1 like L1: