VBA

DATE CONVERTER IN EXCEL

by admin

How to use in Excel 2007:

  1. Install the module:
    • Press Alt + F11 to open VBA editor
    • Go to Insert → Module
    • Paste the entire code above
    • Save the workbook as Macro-Enabled Workbook (.xlsm)
  2. Use the functions in Excel cells:Convert English to Nepali:textCopyDownload=ENGLISH_TO_NEPALI(2024, 1, 15)Returns: “2080-10-02” (example)Convert Nepali to English:textCopyDownload=NEPALI_TO_ENGLISH(2080, 10, 2)Returns: “2024-01-15” (example)
  3. Example with cell references:textCopyDownload=ENGLISH_TO_NEPALI(A1, B1, C1) =NEPALI_TO_ENGLISH(D1, E1, F1)

The module maintains all the original functionality from the PHP code, including:

  • Date validation
  • Full Nepali calendar data (1975-2095 BS)
  • Conversion in both directions
  • Error handling for invalid dates
Option Explicit

' Nepali Date Conversion Module for Excel 2007
' Converts between English (AD) and Nepali (BS) dates

Public Function NEPALI_TO_ENGLISH(ByVal ne_year As Long, ByVal ne_month As Long, ByVal ne_day As Long) As Variant
    ' Convert Nepali date to English date
    ' Returns a string in format "YYYY-MM-DD" or error message
    
    On Error GoTo ErrorHandler
    
    Dim validate As Variant
    validate = ValidateNepaliDate(ne_year, ne_month, ne_day)
    
    If validate <> True Then
        NEPALI_TO_ENGLISH = validate
        Exit Function
    End If
    
    Dim date_start As Date
    date_start = DateSerial(1918, 4, 13)
    
    Dim daycount As Long
    daycount = 0
    
    Dim i As Long, j As Integer
    Dim months As Integer
    months = ne_month - 1
    
    ' Add days from start year to target year
    For i = 1975 To ne_year - 1
        daycount = daycount + SumNepaliMonthDays(i)
    Next i
    
    ' Add days from months before target month
    For j = 0 To months - 1
        daycount = daycount + GetNepaliMonthDays(ne_year, j + 1)
    Next j
    
    ' Add remaining days
    daycount = daycount + ne_day - 1
    
    ' Calculate English date
    Dim english_date As Date
    english_date = date_start + daycount
    
    NEPALI_TO_ENGLISH = Format(english_date, "yyyy-mm-dd")
    Exit Function
    
ErrorHandler:
    NEPALI_TO_ENGLISH = "Error: " & Err.Description
End Function

Public Function ENGLISH_TO_NEPALI(ByVal en_year As Long, ByVal en_month As Long, ByVal en_day As Long) As Variant
    ' Convert English date to Nepali date
    ' Returns a string in format "YYYY-MM-DD" or error message
    
    On Error GoTo ErrorHandler
    
    Dim validate As Variant
    validate = ValidateEnglishDate(en_year, en_month, en_day)
    
    If validate <> True Then
        ENGLISH_TO_NEPALI = validate
        Exit Function
    End If
    
    Dim date_start As Date
    date_start = DateSerial(1918, 4, 13)
    
    Dim current_date As Date
    current_date = DateSerial(en_year, en_month, en_day)
    
    Dim days_diff As Long
    days_diff = current_date - date_start
    
    Dim total_days As Long
    total_days = 0
    
    Dim ne_year As Long, ne_month As Integer, ne_day As Integer
    Dim i As Long, j As Integer
    
    For i = 1975 To 2095
        total_days = total_days + SumNepaliMonthDays(i)
        
        If total_days > days_diff Then
            ne_year = i
            
            Dim prev_total As Long
            prev_total = total_days - SumNepaliMonthDays(i)
            
            For j = 1 To 12
                prev_total = prev_total + GetNepaliMonthDays(i, j)
                If prev_total > days_diff Then
                    ne_month = j
                    ne_day = days_diff - (prev_total - GetNepaliMonthDays(i, j)) + 1
                    Exit For
                ElseIf prev_total = days_diff Then
                    ne_month = j + 1
                    ne_day = 1
                    Exit For
                End If
            Next j
            Exit For
        ElseIf total_days = days_diff Then
            ne_year = i + 1
            ne_month = 1
            ne_day = 1
            Exit For
        End If
    Next i
    
    ENGLISH_TO_NEPALI = FormatDateString(ne_year, ne_month, ne_day)
    Exit Function
    
ErrorHandler:
    ENGLISH_TO_NEPALI = "Error: " & Err.Description
End Function

' Helper functions
Private Function SumNepaliMonthDays(ByVal year As Long) As Long
    ' Calculate total days in a Nepali year
    Dim total As Long
    total = 0
    Dim i As Integer
    For i = 1 To 12
        total = total + GetNepaliMonthDays(year, i)
    Next i
    SumNepaliMonthDays = total
End Function

Private Function GetNepaliMonthDays(ByVal year As Long, ByVal month As Integer) As Integer
    ' Get days in a specific Nepali month
    
    Dim month_days As Variant
    month_days = GetNepaliYearData(year)
    
    If IsArray(month_days) And month >= 1 And month <= 12 Then
        GetNepaliMonthDays = month_days(month - 1)
    Else
        GetNepaliMonthDays = 0
    End If
End Function

Private Function GetNepaliYearData(ByVal year As Long) As Variant
    ' Return array of days for each month of a Nepali year
    
    Select Case year
        Case 1975: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        Case 1976: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        Case 1977: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 1978: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 1979: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        Case 1980: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 1981: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 1982: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        Case 1983: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 1984: GetNepaliYearData = Array(31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 1985: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 1986: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        Case 1987: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 1988: GetNepaliYearData = Array(31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 1989: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 1990: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31)
        Case 1991: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 1992: GetNepaliYearData = Array(31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 1993: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 1994: GetNepaliYearData = Array(30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        Case 1995: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 1996: GetNepaliYearData = Array(31, 31, 32, 32, 31, 30, 29, 30, 30, 29, 30, 30)
        Case 1997: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 1998: GetNepaliYearData = Array(30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        Case 1999: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 2000: GetNepaliYearData = Array(30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        Case 2001: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2002: GetNepaliYearData = Array(31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 2003: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 2004: GetNepaliYearData = Array(30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        Case 2005: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2006: GetNepaliYearData = Array(31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 2007: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 2008: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31)
        Case 2009: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2010: GetNepaliYearData = Array(31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 2011: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 2012: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        Case 2013: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2014: GetNepaliYearData = Array(31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 2015: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 2016: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        Case 2017: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2018: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 2019: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        Case 2020: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2021: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2022: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        Case 2023: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        Case 2024: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2025: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2026: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 2027: GetNepaliYearData = Array(30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        Case 2028: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2029: GetNepaliYearData = Array(31, 31, 32, 31, 32, 30, 30, 29, 30, 29, 30, 30)
        Case 2030: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 2031: GetNepaliYearData = Array(30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        Case 2032: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2033: GetNepaliYearData = Array(31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 2034: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 2035: GetNepaliYearData = Array(30, 32, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31)
        Case 2036: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2037: GetNepaliYearData = Array(31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 2038: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 2039: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        Case 2040: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2041: GetNepaliYearData = Array(31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 2042: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 2043: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        Case 2044: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2045: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 2046: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 2047: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2048: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2049: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        Case 2050: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        Case 2051: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2052: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2053: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        Case 2054: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        Case 2055: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2056: GetNepaliYearData = Array(31, 31, 32, 31, 32, 30, 30, 29, 30, 29, 30, 30)
        Case 2057: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 2058: GetNepaliYearData = Array(30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        Case 2059: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2060: GetNepaliYearData = Array(31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 2061: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 2062: GetNepaliYearData = Array(30, 32, 31, 32, 31, 31, 29, 30, 29, 30, 29, 31)
        Case 2063: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2064: GetNepaliYearData = Array(31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 2065: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 2066: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31)
        Case 2067: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2068: GetNepaliYearData = Array(31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 2069: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 2070: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        Case 2071: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2072: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 2073: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        Case 2074: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2075: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2076: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        Case 2077: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        Case 2078: GetNepaliYearData = Array(31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2079: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2080: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        Case 2081: GetNepaliYearData = Array(31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        Case 2082: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        Case 2083: GetNepaliYearData = Array(31, 31, 32, 31, 31, 30, 30, 30, 29, 30, 30, 30)
        Case 2084: GetNepaliYearData = Array(31, 31, 32, 31, 31, 30, 30, 30, 29, 30, 30, 30)
        Case 2085: GetNepaliYearData = Array(31, 32, 31, 32, 30, 31, 30, 30, 29, 30, 30, 30)
        Case 2086: GetNepaliYearData = Array(30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30)
        Case 2087: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 30, 29, 30, 30, 30)
        Case 2088: GetNepaliYearData = Array(30, 31, 32, 32, 30, 31, 30, 30, 29, 30, 30, 30)
        Case 2089: GetNepaliYearData = Array(30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30)
        Case 2090: GetNepaliYearData = Array(30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30)
        Case 2091: GetNepaliYearData = Array(31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        Case 2092: GetNepaliYearData = Array(30, 31, 32, 32, 31, 30, 30, 30, 29, 30, 30, 30)
        Case 2093: GetNepaliYearData = Array(30, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        Case 2094: GetNepaliYearData = Array(31, 31, 32, 31, 31, 30, 30, 30, 29, 30, 30, 30)
        Case 2095: GetNepaliYearData = Array(31, 31, 32, 31, 31, 31, 30, 29, 30, 30, 30, 30)
        Case Else: GetNepaliYearData = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
    End Select
End Function

Private Function ValidateNepaliDate(ByVal year As Long, ByVal month As Long, ByVal day As Long) As Variant
    ' Validate Nepali date
    
    If year < 1975 Or year > 2095 Then
        ValidateNepaliDate = "Invalid Year range (1975-2095)"
        Exit Function
    End If
    
    If month < 1 Or month > 12 Then
        ValidateNepaliDate = "Invalid Month range (1-12)"
        Exit Function
    End If
    
    Dim max_days As Integer
    max_days = GetNepaliMonthDays(year, month)
    
    If day < 1 Or day > max_days Then
        ValidateNepaliDate = "Invalid Day for this month"
        Exit Function
    End If
    
    ValidateNepaliDate = True
End Function

Private Function ValidateEnglishDate(ByVal year As Long, ByVal month As Long, ByVal day As Long) As Variant
    ' Validate English date
    
    If year < 1918 Or year > 2038 Then
        ValidateEnglishDate = "Invalid Year range (1918-2038)"
        Exit Function
    End If
    
    If month < 1 Or month > 12 Then
        ValidateEnglishDate = "Invalid Month range (1-12)"
        Exit Function
    End If
    
    Dim max_days As Integer
    max_days = Day(DateSerial(year, month + 1, 0))
    
    If day < 1 Or day > max_days Then
        ValidateEnglishDate = "Invalid Day for this month"
        Exit Function
    End If
    
    ValidateEnglishDate = True
End Function

Private Function FormatDateString(ByVal year As Long, ByVal month As Integer, ByVal day As Integer) As String
    ' Format date as YYYY-MM-DD
    
    FormatDateString = Format(year, "0000") & "-" & Format(month, "00") & "-" & Format(day, "00")
End Function