Using Excel's functions with Hijri calendar

[EyadAlharbi]

Hi,

I have an excel sheet where I'm using both the Hijri and Gregorian calendars in it.

I'm trying to get the excel sheet to bring the today's date and then break it into year, month, and day values separately in each cell.

my idea is to use the "=today()" formula to return the today's date and then use the functions: "day( )" ,  "month( )" ,  "year ( )".

now for the Gregorian date it works flawlessly, but for the Hijri date it returns the Gregorian values even though the date is converted using the cell format. I'm not really sure about how to get the excel to return the Hijri values. I tried using the Left and Right function but it didn't work as well. 

 

[#2]

@EyadAlharbi 

There are many roads leading to Rome, and there are many roads leading to a solution in Excel.

You could do it with cell formatting, too, easily and quickly.

On the desired cell where the day, month or year should be, simply connect to the date cell.

Example: Click on cell C3 and enter "= A3" as the formula (without quotes).

The value (date) of cell A3 appears in cell C3.

Then on cell C3

press the right mouse button,

Format cells ... select,

go to Custom,

for type: insert DD for day, or MM for month, or YYYY for year.

Press Ok and you will only see the desired date, month or year.

Examples with this option and with formulas can be found in the inserted example file.

Hope I was able to help you with this info.

Nikolino

I know I don't know anything (Socrates)

[#3]

Thank you @NikolinoDE for your response.

I tried your method but I'm still having the same issue. see the outputs in row 2

The desired/correct outputs for row 2 should be: 21 for cell C3, 04 for cell D3,and 1443 for cell E3.

Regards,

[#4 · Accepted solution]

@EyadAlharbi 

If apply correct format it shown correctly

[#5]

Thank you Sergei. It worked!

[#6]

@EyadAlharbi , you are welcome, glad it helped