• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Increment hijri date by one year

YasserKhalil

YasserKhalil

Well-Known Member
Hello everyone
I am searching for a solution to be able to add one year to the Hijri date
this is an example:
Code: 
1437/10/05
The year 1437 would be 1438

Is there a UDF function that deal with Hijri dates to be able to add days or months or yeas as it is done with Gregorian dates
 
Thanks a lot Mr. Hui I tried your solution but got VALUE error
As for the links they deal with the conversion between Hijri dates and Gregorian dates .. I don't need conversion I just need to deal with these Hijri dates flexibly ..to be able to add days or months or years
Thanks for offering this great help
 
Check post#6 from last link provided by Narayan.

That should give you starting point to construct formula.
 
It will largely depend on how your workbook is set up. Can you upload sample? Most importantly, is the hijri date in date value? Or is it in text format?
 
Here's a sample workbook
I have put a formula that do the task but I need to find a more perfect way to deal with Hijri dates
 

Attachments

  • Increment Date One Year.xlsm
    13.4 KB · Views: 20
Hi ,

The sole point is whether you will use only Hijri dates in your workbooks or will it be only this workbook or will it be a mixture of both Gregorian and Hijri dates.

If it is the first , then changing your Windows setting allows you to use Hijri dates in Excel the way you will use normal dates. Excel will accept all Hijri dates.

If it is the second or the third , then using the format suggested in the links allows you to enter all dates as Gregorian dates , but displays them as Gregorian or Hijri depending on the cell formats.

In either case , since the EDATE function is working on valid Excel dates , which are stored as Gregorian dates , the formula suggested by Hui will work.

A date cannot be entered in Hijri format unless the Windows setting is changed ; Excel will not accept Hijri dates as valid dates , and arithmetic operations cannot be done on them ; you will have to develop the formulae to do them.

Narayan
 
This is the problem for me .. the problem is that I would use both dates in some of my workbooks and I hope to find an easy and flexible way to deal with both of the dates ..
 
If it was me, I'd convert all dates to Gregorian date and when needed convert output to Hijri date. Rather than try to work with two separate date system.
 
If its stored not as text but in date value, EDATE(x,12) will return 1 Gregorian year later (i.e. 364 or 365 days later). 1438/22/05 from example given, instead of 1438/10/05

So to get 1 Hijri year later, would be either 354 or 355 days later.
Assuming date is in A1 and is date value, below formula would add 1 year accounting for leap years.

=IF(TEXT(A1,"B2dd")=TEXT(A1+354,"B2dd"),A1+354,A1+355)

So similar logic can be used in UDF.
 
Last edited:
Thanks a lot for this great help Mr. Chihiro..
I tried your formula but it gives me VALUE error ..why?
I tried to change the format of A1 with diifferent formats and the same error
Can you upload a sample workbook with the formula to see the result?
 
Sure, see attached (Col T:V). Your issue is that your date is stored as text and not date value. You have to first convert your text date to value (either by doing calculation or by constructing Lookup Table), then use formula.
 

Attachments

  • Increment Date One Year.xlsm
    12.7 KB · Views: 34
Thank you very very much Mr. Chihiro
You are a HERO Mr. Hiro
Thanks for great help in this thread ..
Best and Kind Regards
 
You must log in or register to reply here.
Back
Top