There is no simple formula for converting Nepali dates in Excel, because Bikram Sambat months change length every year — a month can have 29, 30, 31 or 32 days. So the workbook below carries a table of every month's length from BS 1978 to 2099, and converts with ordinary INDEX and MATCH formulas. It needs no macros, no add-ins and no internet, and it gives the same result as the date converter on this site.
What's in the workbook
Four sheets; you only type in the yellow cells.
- Converter — type a BS year, month and day to get the English date, or type an English date to get the Nepali one.
- Bulk AD to BS — paste up to 500 English dates in column A; the Nepali dates fill in beside them.
- Bulk BS to AD — enter year, month and day in three columns to get English dates.
- BS_Data — the calendar table the formulas read: month lengths and the English date of 1 Baisakh for each year.
The formulas, if you want to build your own
BS to AD adds three things: the English date of 1 Baisakh that year, the days in the months before your month, and your day minus one. With the year in B5, month in B6 and day in B7:
=INDEX(Start, MATCH(B5, Years, 0)) + INDEX(CumDays, MATCH(B5, Years, 0), B6) + B7 − 1
AD to BS works backwards: MATCH finds the last 1 Baisakh on or before your date (that row is the BS year), the difference gives the day of the year, and a second MATCH across that year's running month totals gives the month. The workbook shows each step in its own cell so you can follow it.
Using it in Google Sheets
Open Google Sheets, choose File → Import → Upload, and select the downloaded file. The formulas work unchanged. To convert your own list, copy the formula row down as far as you need.
How accurate is it?
Every day from 1 Baisakh 1978 to the end of 2099 BS was checked against this site's converter — 44,562 days — and all matched. The 2083 month lengths match the Government of Nepal's 2083 holiday gazette. Years after 2083 follow the commonly published calendar, which is occasionally adjusted when the official calendar committee publishes a new year; download the latest file if a date near a month's end matters.
Why do some online converters disagree by a day? Many use month-length tables that were never corrected for recent years. If two tools differ, check the date against the official patro or the government holiday list.