In one of my earlier videos, I have explained in detail how to handle Arabic/Hijri dates in Excel <<Video Link>>.
Well, I was asked this time, is it possible to calculate Arabic or Hijri age in Excel, well my answer is yes, everything is possible in Excel.
In Excel, we have a very easy function DATEDIF that helps us to calculate age with a gregorian calendar.
So, let us see how to build Hijri / Arabic Age Calculator in Excel.
Hijri Age Calculator
Enter your date of birth and see your Hijri Age (Note: Entered dates are nowhere stored in our records.)
Basic understanding of Arabic or Hijri calander
Hijri calendar started after the migration of the Prophet Mohammed (PBUH ﷺ) from Makkah to Medina. Hijri calendar or Arabic calendar widely used in Islamic countries. It is also known as the Lunar calendar. It contains 12 Islamic months that are depending on the moon’s motion.
What is the lunar calendar?
A lunar calendar is based on the monthly cycles of the Moon’s phases (synodic months or lunations). An average lunar orsynodic month lasts for 29.530575 days or 29 days, 12 hours, 44 minutes, and 2 seconds. For more details, you can refer to this link
These are basic things we need to know if you want to calculate Hijri age.
Hijri Age Calculation
Let us consider the age who is born on 20th August 1987 will be “34 years 02 months 27days” as of 16th November 2021. But in Hijri, his/her age will be “35 years 03 months 15 days”
How did we derive to that Hijri age?
- 12 Islamic months x Average lunar Days 29.530575 = 354.366900 Number of days in a Hijri Year.
- We need to calculate the number of days from the born date
- 16th November 2021 – 20th August 1987 = 12507 Age in days
You can use the DATEDIF formula to get the difference
- 12507 Age in days/354.366900 No of days in Hijri Year = 35.293928 Age In Years
The integer value is the age 35 years in Hijri & decimal value is the months (0.2939280)
- 0.293928 x 12 Islamic months = 3.527136 Number of Month
The integer value is the age 03 months in Hijri & decimal value days (0.527136)
- 0.527136 x Average lunar Days 29.530575 = 15.566629 Number of days
We will take only the integer value and we land up to 15 days
When combine all those numbers, it is “35 years 03 months 15 days”
Excel Old-School formula to calculate hijri age
Copy this formula and simply replace the [DOB] with your desired cell name.
The above formula works with Microsoft Excel 2007 onwards
LAMBDA to calculate hijri age in Excel
Copy this LAMDA function and paste it into the advanced formula editor
The above formula works as of now with Office 365 subscription users only
Was the information helpful in this blog?
- If yes, hit that share button and show the excitement to the world.
- Subscribe to our free posts.
If no, please let us know what to improve.
Having trouble with any Office Apps. Feel free to ask and answer queries at our forums section.