Bifurcate total expenses on the basis of calendar months other than AD

[MandeepBajimaya]

I have an Excel file where the total expenses for a certain period are mentioned. The issue is the dates are in Bikram Sambat calender (This is in Nepali calender which is not recognized by Excel). I want them to be allocated on the basis of months. EG: If I have paid expenses from 4th April to 3rd July, the amount should be allocated for 26 days of April, 31 days of May, 30 days of June and 3 days of July. Unfortunately for BS calender. I have tried some working as well but couldn't succeed though. I have also attached my workings.

Nepali expenses.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
3	Year Start	2079
4	Year End	2080
5
6
7					31	31	31	30	29	30	29	30	30	31	32	31	365
8					04	05	06	07	08	09	10	11	12	01	02	03
9					2079.04	2079.05	2079.06	2079.07	2079.08	2079.09	2079.10	2079.11	2079.12	2080.01	2080.02	2080.03
10					Shrawan	Bhadra	Asoj	Kartik	Mangsir	Poush	Magh	Falgun	Chaitra	Baishakh	Jestha	Ashadh	Total
11	01.04.2079	30.12.2079	271	12,000	1,373	1,373	1,373	1,328	1,284	1,328	1,284	1,328	1,328	-	-	-	12,000
12	01.04.2079	25.11.2079	236	12,000	1,576	1,576	1,576	1,525	1,475	1,525	1,475	-	1,525	-	-	-	12,254
13			1	12,000	1,576	1,576	1,576	1,525	1,475	1,525	1,475	1,525	1,525				13,780
14			1	12,000													-
15	2079.04.01	2079.11.25	236	12,000	1,576	1,576	1,576	1,525	1,475	1,525	1,475	-	-	-	-	1,576	12,305
16			1	12,000													-
17			1	12,000	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		-
18			1	12,000													-
19			1	12,000													-
20			1	12,000													-
Sheet1

Cell Formulas
Range	Formula
C4	C4	=C3+1
R7,R11:R20	R7	=SUM(F7:Q7)
F9	F9	=$C$3&".04"
G9	G9	=$C$3&".05"
H9	H9	=$C$3&".06"
I9	I9	=$C$3&".07"
J9	J9	=$C$3&".08"
K9	K9	=$C$3&".09"
L9	L9	=$C$3&".10"
M9	M9	=$C$3&".11"
N9	N9	=$C$3&".12"
O9	O9	=$C$4&".01"
P9	P9	=$C$4&".02"
Q9	Q9	=$C$4&".03"
F11:Q12	F11	=IF(AND(OR(VALUE(RIGHT($B11,4))=$C$3,VALUE(RIGHT($B11,4))=$C$4),MID($B11,4,2)<=F$8,OR(MID($C11,4,2)>=G$8,VALUE(RIGHT($C11,4))=$C$4)),$E11/$D11*F$7,0)
F13:N13	F13	=IF(AND(VALUE(RIGHT($B12,4))=$C$3,MID($B12,4,2)<=F$8),$E12/$D12*F$7,0)
F15:Q15	F15	=IF(AND(LEFT($B15,7)<=F$9,LEFT($C15,7)>=G$9),$E15/$D15*F$7,0)
F17:P17	F17	=F9>G9
D11:D20	D11	=DATEDIF(B2A(B11),B2A(C11),"d")+1

 

[Skybluekid]

What difference is there between the Bikram Sambat calender and the Gregorian Calendar? As in your example it seems to have the same number of days in April, May and June.

 

[MandeepBajimaya]

What difference is there between the Bikram Sambat calender and the Gregorian Calendar? As in your example it seems to have the same number of days in April, May and June.

In some months, there are 32 days in one month and 32 days in a month is not considered by Excel. If you see the excel file, there is 32 days in second last month.