DATE CONVERTER IN EXCEL – V2.0
Option Explicit
' Nepali Date Conversion Module for Excel 2007
' Converts between English (AD) and Nepali (BS) dates
Public Function NEPALI_TO_ENGLISH(ByVal ne_date As String) As Variant
' Convert Nepali date to English date
' Input: "YYYY-MM-DD" format (month and day can be with or without leading zeros)
' Returns a string in format "YYYY-MM-DD" or error message
On Error GoTo ErrorHandler
' Parse the date string
Dim ne_year As Long, ne_month As Long, ne_day As Long
If Not ParseDateString(ne_date, ne_year, ne_month, ne_day) Then
NEPALI_TO_ENGLISH = "Invalid date format. Please use YYYY-MM-DD"
Exit Function
End If
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_date As String) As Variant
' Convert English date to Nepali date
' Input: "YYYY-MM-DD" format (month and day can be with or without leading zeros)
' Returns a string in format "YYYY-MM-DD" or error message
On Error GoTo ErrorHandler
' Parse the date string
Dim en_year As Long, en_month As Long, en_day As Long
If Not ParseDateString(en_date, en_year, en_month, en_day) Then
ENGLISH_TO_NEPALI = "Invalid date format. Please use YYYY-MM-DD"
Exit Function
End If
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 function to parse date string - Updated to handle variable formats
Private Function ParseDateString(ByVal dateStr As String, ByRef year As Long, ByRef month As Long, ByRef day As Long) As Boolean
' Parse a date string in "YYYY-MM-DD" format
' Handles: YYYY-MM-DD, YYYY-M-DD, YYYY-MM-D, YYYY-M-D
On Error GoTo ParseError
' Remove any leading/trailing spaces
dateStr = Trim(dateStr)
' Split the string by hyphen
Dim parts As Variant
parts = Split(dateStr, "-")
' Check if we have exactly 3 parts
If UBound(parts) <> 2 Then
ParseDateString = False
Exit Function
End If
' Validate year (must be 4 digits)
If Len(parts(0)) <> 4 Or Not IsNumeric(parts(0)) Then
ParseDateString = False
Exit Function
End If
' Validate month (1-2 digits)
If Not IsNumeric(parts(1)) Then
ParseDateString = False
Exit Function
End If
' Validate day (1-2 digits)
If Not IsNumeric(parts(2)) Then
ParseDateString = False
Exit Function
End If
' Extract parts
year = CLng(parts(0))
month = CLng(parts(1))
day = CLng(parts(2))
' Basic validation
If month < 1 Or month > 12 Then
ParseDateString = False
Exit Function
End If
If day < 1 Or day > 31 Then
ParseDateString = False
Exit Function
End If
ParseDateString = True
Exit Function
ParseError:
ParseDateString = False
End Function
' Helper functions (unchanged)
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
