Sitemap

Excel Add-in: AD ↔ BS Date Converter

Create your own powerful Microsoft Excel add-in to instantly convert dates between the Gregorian (AD) calendar and the Bikram Sambat (BS) calendar used in Nepal.
No internet required. Works offline forever.

How to Install the Add-in (Step-by-Step)

  1. Open a new blank Excel workbook.
  2. Press Ctrl + F11 to open the Visual Basic for Applications (VBA) editor.
  3. In the VBA editor, go to Insert → Module.
  4. Copy and paste the entire VBA code below into the new module window.
  5. Close the VBA editor, then go to File → Save As.
    Choose “Excel Add-in” (*.xlam) as the file type.
    Name it something like AD_BS_Converter.xlam and click Save.
    Excel will automatically save it to your Add-ins folder.
  6. Open any Excel workbook.
  7. Go to File → Options → Add-ins.
    At the bottom, next to “Manage”, select “Excel Add-ins” → Click Go.
    Check the box for AD_BS_Converter (or whatever name you gave) and click OK.
  8. That’s it! The add-in is now installed and ready to use.

Complete VBA Code (Copy & Paste)


                        Public Starting_Range_AD As Date
Public BS(0 To 100) As Variant

Sub BS_Database()
    Starting_Range_AD = DateSerial(1943, 4, 14)
    BS(0) = Array(2000, 30, 32, 31, 32, 31, 30, 29, 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, 32, 31, 31, 30, 30, 30, 29, 29, 30, 31)
    BS(8) = Array(2008, 31, 31, 32, 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, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
    BS(82) = Array(2082, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
    BS(83) = Array(2083, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 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)
End Sub

Function Smart_Parse_BS(ByVal inputStr As String) As Variant
    Dim parts As Variant
    Dim a As Long, b As Long, c As Long
    Dim yearVal As Long, monVal As Long, dayVal As Long
    
   
    inputStr = Replace(Replace(Replace(inputStr, ".", "/"), "-", "/"), "\", "/")
    parts = Split(inputStr, "/")
    
    If UBound(parts) <> 2 Then
        Smart_Parse_BS = Empty
        Exit Function
    End If
    
    a = Val(Trim(parts(0)))
    b = Val(Trim(parts(1)))
    c = Val(Trim(parts(2)))
    
 
    If a > 100 Then
        yearVal = a: monVal = b: dayVal = c
    ElseIf c > 100 Then
        yearVal = c: monVal = b: dayVal = a
    ElseIf b > 100 Then
        yearVal = b: monVal = a: dayVal = c
    
   
    Else
        
        If a >= 0 And a <= 99 Then
            yearVal = IIf(a <= 91, 2000 + a, 1900 + a)
            monVal = b
            dayVal = c
        ElseIf c >= 0 And c <= 99 Then
            yearVal = IIf(c <= 91, 2000 + c, 1900 + c)
            monVal = b
            dayVal = a
        Else
            
            yearVal = IIf(a <= 91, 2000 + a, 1900 + a)
            monVal = b
            dayVal = c
        End If
    End If
    
   
    If monVal < 1 Or monVal > 12 Or dayVal < 1 Or dayVal > 32 Or yearVal < 1900 Then
        Smart_Parse_BS = Empty
    Else
        Smart_Parse_BS = Array(yearVal, monVal, dayVal)
    End If
End Function

' ==================== AD to BS ====================
Function AD2BS(ByVal AD As Variant) As String
    If Not IsDate(AD) Then
        AD2BS = "Invalid Date": Exit Function
    End If
    
    Call BS_Database
    
    Dim DD_Gap As Long, DD_total As Long, i As Long, ii As Long
    
    DD_Gap = DateDiff("d", Starting_Range_AD, CDate(AD))
    If DD_Gap < 0 Then
        AD2BS = "Out of Range": Exit Function
    End If
    
    DD_total = 0
    For i = LBound(BS) To UBound(BS)
        If IsEmpty(BS(i)) Then GoTo NextYear
        
        For ii = 1 To 12
            DD_total = DD_total + BS(i)(ii)
            
            If DD_total > DD_Gap Then
                DD_total = DD_total - BS(i)(ii)
                AD2BS = Format(BS(i)(0), "0000") & "/" & _
                        Format(ii, "00") & "/" & _
                        Format(DD_Gap - DD_total + 1, "00")
                Exit Function
            End If
        Next ii
NextYear:
    Next i
    
    AD2BS = "Out of Range"
End Function

' ==================== BS to AD ====================
Function BS2AD(ByVal BSDate As String) As String
    Call BS_Database
    
    Dim p As Variant, i As Long, ii As Long, DD_total As Long
    
    p = Smart_Parse_BS(BSDate)
    If IsEmpty(p) Then
        BS2AD = "Format Error": Exit Function
    End If
    
    DD_total = 0
    For i = LBound(BS) To UBound(BS)
        If IsEmpty(BS(i)) Then GoTo NextYear
        
        If BS(i)(0) < p(0) Then
            For ii = 1 To 12
                DD_total = DD_total + BS(i)(ii)
            Next ii
            
        ElseIf BS(i)(0) = p(0) Then
            For ii = 1 To p(1) - 1
                DD_total = DD_total + BS(i)(ii)
            Next ii
            DD_total = DD_total + (p(2) - 1)
            
            BS2AD = Format(Starting_Range_AD + DD_total, "yyyy/mm/dd")
            Exit Function
        End If
NextYear:
    Next i
    
    BS2AD = "Out of Range"
End Function

Sub Batch_Convert_Dates()
    Dim rng As Range, cell As Range, mode As VbMsgBoxResult
    
    On Error Resume Next
    Set rng = Application.InputBox("Select range:", "Batch Convert", Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    
    mode = MsgBox("YES: AD to BS" & vbCrLf & "NO: BS to AD", vbYesNoCancel)
    If mode = vbCancel Then Exit Sub
    
    Application.ScreenUpdating = False
    
    For Each cell In rng
        If cell.Value <> "" Then
            If mode = vbYes Then
                cell.Offset(0, 1).Value = AD2BS(cell.Value)
            Else
                cell.Offset(0, 1).Value = BS2AD(CStr(cell.Value))
            End If
            cell.Offset(0, 1).NumberFormat = "@"   ' Treat as text
        End If
    Next cell
    
    Application.ScreenUpdating = True
End Sub

After installation, simply use the functions =AD2BS(A1) or =BS2AD(A1) in any cell.
Or run the macro Batch_Convert_Dates for bulk conversion!