convert english calendar to nepali and vice versa

[shambhu]

i want to convert english date to nepali date and vice versa as follows. How can it be done? This example is just for a month, what if i need to convert the date for about 10 years.
[TABLE="width: 217"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]English Date[/TD]
[TD]Nepali date[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/1[/TD]
[TD="align: right"]2072/4/16[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/2[/TD]
[TD="align: right"]2072/4/17[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/3[/TD]
[TD="align: right"]2072/4/18[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/4[/TD]
[TD="align: right"]2072/4/19[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/5[/TD]
[TD="align: right"]2072/4/20[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/6[/TD]
[TD="align: right"]2072/4/21[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/7[/TD]
[TD="align: right"]2072/4/22[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/8[/TD]
[TD="align: right"]2072/4/23[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/9[/TD]
[TD="align: right"]2072/4/24[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/10[/TD]
[TD="align: right"]2072/4/25[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/11[/TD]
[TD="align: right"]2072/4/26[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/12[/TD]
[TD="align: right"]2072/4/27[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/13[/TD]
[TD="align: right"]2072/4/28[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/14[/TD]
[TD="align: right"]2072/4/29[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/15[/TD]
[TD="align: right"]2072/4/30[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/16[/TD]
[TD] 2072/4/31[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/17[/TD]
[TD] 2072/4/31[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/18[/TD]
[TD="align: right"]2072/5/1[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/19[/TD]
[TD="align: right"]2072/5/2[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/20[/TD]
[TD="align: right"]2072/5/3[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/21[/TD]
[TD="align: right"]2072/5/4[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/22[/TD]
[TD="align: right"]2072/5/5[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/23[/TD]
[TD="align: right"]2072/5/6[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/24[/TD]
[TD="align: right"]2072/5/7[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/25[/TD]
[TD="align: right"]2072/5/8[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/26[/TD]
[TD="align: right"]2072/5/9[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/27[/TD]
[TD="align: right"]2072/5/10[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/28[/TD]
[TD="align: right"]2072/5/11[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/29[/TD]
[TD="align: right"]2072/5/12[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/30[/TD]
[TD="align: right"]2072/5/13[/TD]
[/TR]
[TR]
[TD="align: right"]2015/8/31[/TD]
[TD="align: right"]2072/5/14[/TD]
[/TR]
</tbody>[/TABLE]

 

[Trevor G]

Have you searched the Net. See this link Nepali Functions Addin for Excel V3 | Sajan Maharjan

 

[shambhu]

Thank you trevor for your help but i need to learn how? I am a learner.

 

[Michael M]

Try

=DATE(YEAR(A1)+56,MONTH(A1)+8, DAY(A1)+16)

 

[shambhu]

there is difference upto 3 days in some of the dates for eg 2015/8/17 to 2072/4/31. so this code you have given will not work for all.

 

[Michael M]

Is the criteria not correct ??
56 years, 8 months and 16 days ??
Are the Nepali months a different length to the English months ??

 

[Michael M]

As Nepali Months vary from 29 to 32 days ( a difference of 3 days), I'd suggest this is the difference in the formula results !
You will have to modify your formula to match certain months to allow for this !
As I don't know how long each month is, I can't help you !!

 

[rampaudel]

User the below link for vba code

Nepali date functions Add-in for excel and VBA code explanation

Often, we have to deal with Nepali dates in excel. Though, there is no built in functions for dealing with Nepali dates, I have made a simp...

blog.anilz.net