English to Nepali Date Converter in Excel: Step-by-Step Guide

Managing dates across different calendars can be a challenge, especially when dealing with international projects or specific regional requirements. If you’re looking for a convenient solution to convert dates from the English calendar to the Nepali calendar, Excel can come to your rescue. In this blog post, we will guide you through the process of creating an English to Nepali date converter in Excel using VBA code. With this converter, you can effortlessly convert dates and streamline your date management tasks.

Understanding the English to Nepali Date Conversion:

The Nepali calendar, also known as the Bikram Sambat calendar, differs from the English calendar in terms of its year count and months. To perform the conversion, we will utilize VBA code that takes an English date as input and calculates the corresponding Nepali date.

Step-by-Step Guide:

1. Open Excel and access the Visual Basic for Applications (VBA) editor by pressing ALT+F11.
2. Insert a new module and paste the provided VBA code into the module window.
3. Save the VBA code and exit the editor, returning to your Excel worksheet.
4. Choose a cell where you want to display the converted Nepali date.
5. Enter the formula “=AD2BS(A2)” in the chosen cell.
6. Replace “A2” with the cell reference containing the English date you wish to convert.
7. Press Enter to execute the formula and observe the converted Nepali date.

Benefits of Using the English to Nepali Date Converter in Excel:

1. Efficiency: The converter saves time and effort by automating the date conversion process, eliminating the need for manual calculations.
2. Accuracy: The VBA code ensures precise date conversion, accounting for variations in calendar systems and leap years.
3. Customizability: You can modify the VBA code to suit specific requirements, such as accommodating different starting years or incorporating additional functionalities.
4. Convenience: With the converter integrated into Excel, you have a user-friendly tool readily available for seamless date conversions whenever needed.

You May Like :

Basics Excel Formulas and Their Practical Uses

All Engineering Related Posts Here

Conclusion:

By following the step-by-step guide outlined in this blog post, you can create an efficient English to Nepali date converter in Excel. This powerful tool enables you to effortlessly convert dates between the English and Nepali calendars, enhancing your date management capabilities. Save time and ensure accuracy in your projects, whether you’re dealing with international collaborations or local Nepali date requirements. Embrace the convenience and reliability of the English to Nepali date converter in Excel and streamline your date-related tasks today.

Remember, with Excel’s versatility and the provided VBA code, you can explore further customization options to cater to specific needs. Enjoy the convenience of accurate date conversion and boost your productivity in managing dates effectively within Excel.

VBA Code

'==========================================================
'created by EdunepalPRO
'https://edunepalpro.com/
'contact email : [email protected]
'==========================================================

Public Starting_Range_AD As Date 'Public variable declration for database starting date in AD i.e. 2000-01-01 is 1943-04-14 AD
Public BS(91) 'Declaring BS database array variable
Public Array_Size As Double 'array size variable for array size calcuation and storing

Sub BS_Database()
    Starting_Range_AD = "1943-04-14" 'database starting date in AD i.e. 2000-01-01 is 1943-04-14 AD
    
    '========================================================================
    'all the BS database array starts with year and total days for each subsequent 12 months
    BS(0) = Array(2000, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
    BS(1) = Array(2001, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(2) = Array(2002, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
    BS(3) = Array(2003, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
    BS(4) = Array(2004, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
    BS(5) = Array(2005, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(6) = Array(2006, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
    BS(7) = Array(2007, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
    BS(8) = Array(2008, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31)
    BS(9) = Array(2009, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(10) = Array(2010, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
    BS(11) = Array(2011, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
    BS(12) = Array(2012, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
    BS(13) = Array(2013, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(14) = Array(2014, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
    BS(15) = Array(2015, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
    BS(16) = Array(2016, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
    BS(17) = Array(2017, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(18) = Array(2018, 31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30)
    BS(19) = Array(2019, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
    BS(20) = Array(2020, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(21) = Array(2021, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(22) = Array(2022, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
    BS(23) = Array(2023, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
    BS(24) = Array(2024, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(25) = Array(2025, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(26) = Array(2026, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
    BS(27) = Array(2027, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
    BS(28) = Array(2028, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(29) = Array(2029, 31, 31, 32, 31, 32, 30, 30, 29, 30, 29, 30, 30)
    BS(30) = Array(2030, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
    BS(31) = Array(2031, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
    BS(32) = Array(2032, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(33) = Array(2033, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
    BS(34) = Array(2034, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
    BS(35) = Array(2035, 30, 32, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31)
    BS(36) = Array(2036, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(37) = Array(2037, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
    BS(38) = Array(2038, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
    BS(39) = Array(2039, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
    BS(40) = Array(2040, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(41) = Array(2041, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
    BS(42) = Array(2042, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
    BS(43) = Array(2043, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
    BS(44) = Array(2044, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(45) = Array(2045, 31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30)
    BS(46) = Array(2046, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
    BS(47) = Array(2047, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(48) = Array(2048, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(49) = Array(2049, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
    BS(50) = Array(2050, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
    BS(51) = Array(2051, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(52) = Array(2052, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(53) = Array(2053, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
    BS(54) = Array(2054, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
    BS(55) = Array(2055, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(56) = Array(2056, 31, 31, 32, 31, 32, 30, 30, 29, 30, 29, 30, 30)
    BS(57) = Array(2057, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
    BS(58) = Array(2058, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
    BS(59) = Array(2059, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(60) = Array(2060, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
    BS(61) = Array(2061, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
    BS(62) = Array(2062, 30, 32, 31, 32, 31, 31, 29, 30, 29, 30, 29, 31)
    BS(63) = Array(2063, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(64) = Array(2064, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
    BS(65) = Array(2065, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
    BS(66) = Array(2066, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31)
    BS(67) = Array(2067, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(68) = Array(2068, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
    BS(69) = Array(2069, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
    BS(70) = Array(2070, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
    BS(71) = Array(2071, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(72) = Array(2072, 31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30)
    BS(73) = Array(2073, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
    BS(74) = Array(2074, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(75) = Array(2075, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(76) = Array(2076, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
    BS(77) = Array(2077, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
    BS(78) = Array(2078, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(79) = Array(2079, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(80) = Array(2080, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
    BS(81) = Array(2081, 31, 31, 32, 32, 31, 30, 30, 30, 29, 30, 30, 30)
    BS(82) = Array(2082, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30)
    BS(83) = Array(2083, 31, 31, 32, 31, 31, 30, 30, 30, 29, 30, 30, 30)
    BS(84) = Array(2084, 31, 31, 32, 31, 31, 30, 30, 30, 29, 30, 30, 30)
    BS(85) = Array(2085, 31, 32, 31, 32, 30, 31, 30, 30, 29, 30, 30, 30)
    BS(86) = Array(2086, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30)
    BS(87) = Array(2087, 31, 31, 32, 31, 31, 31, 30, 30, 29, 30, 30, 30)
    BS(88) = Array(2088, 30, 31, 32, 32, 30, 31, 30, 30, 29, 30, 30, 30)
    BS(89) = Array(2089, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30)
    BS(90) = Array(2090, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30)
    BS(91) = Array(2091, 31, 31, 32, 31, 31, 31, 30, 30, 29, 30, 30, 30)
    
    Array_Size = UBound(BS) 'storing the value of array size
End Sub

Function AD2BS(AD As Date) As String 'Function for converting AD to BS
    Call BS_Database 'Calling the database in memory

    DD_Gap = DateDiff("d", Starting_Range_AD, AD) 'calculating the days difference between entered AD date and starting database AD date
    
    If DD_Gap < 0 Then GoTo Out_Of_Range 'condition for checking whether the entered AD date is befor the starting AD date in the database
    
    DD_total = 0 'creating variable for cummulative days in the database
    
    For i = 0 To Array_Size 'Loop for each year
        For ii = 1 To 12 'Loop for each month
            DD_total = DD_total + BS(i)(ii) 'calculating cummulative days by adding days in each months
            
            If DD_total > DD_Gap Then 'checking whether the cummulative days is greater than DD_Gap variable
                DD_total = DD_total - BS(i)(ii) 'reversing the last addition of days
                GoTo Return_Value
            End If
            
        Next
    Next

Out_Of_Range:

    AD2BS = "Error! Out of range..." 'returning error

Exit Function

Return_Value:

    AD2BS = BS(i)(0) & "-" & ii & "-" & (DD_Gap - DD_total + 1) 'returning BS value

End Function