calculate salary

[gssachin]

Hi,

I want to pay a salary of 50000 INR pm from 10-04-2020 to 18-08-2020. We are calculating working days for April, may, June, July & Aug after removing Fridays (i.e. for apr – 26 days, may – 26 days, jun & July -26 days & Aug 27 days

I m using networkdays.int formula for calculating days

i.e. NETWORKDAYS.INTL(10-04-2020,(18-08-2020-1),16) = 112 days

so I need formula/macro to calculate this so system will calculate

(10-04-2020 to 30-04-2020)/26* Amt (April working days after removing Friday )

(01-05-2020 to 31-05-2020)/26* Amt (May working days after removing Friday )

(01-06-2020 to 30-06-2020)/26* Amt (June working days after removing Friday )

(01-07-2020 to 31-07-2020)/26 * Amt (July working days after removing Friday )

(01-08-2020 to 18-08-2020)/27 * Amt (Aug working days after removing Friday )

 

[Dave Patton]

Your information is unclear but the following may help.

T202006b.xlsm
	A	B	C	D	E
1	Amount			50000
2	Term	10-Apr-20	18-Aug-20	112	446.4286
3
4		10-Apr-20	30-Apr-20	18	8035.714
5		01-May-20	31-May-20	26	11607.14
6		01-Jun-20	30-Jun-20	26	11607.14
7		01-Jul-20	31-Jul-20	26	11607.14
8		01-Aug-20	18-Aug-20	16	7142.857
9					50000
10
2a

Cell Formulas
Range	Formula
D2,D4:D8	D2	=NETWORKDAYS.INTL(B2,C2,16)
E2	E2	=D1/D2
E4:E8	E4	=$E$2*D4
E9	E9	=SUM(E4:E8)

 

[gssachin]

Dear Sir,

Sorry for the inconvenience. I want to get a total payable salary in one cell only. also, salary should calculate 50000/no of working days for each month (after removing Friday)*no of days work (i.e. d column in your calculation) And my Answer is INR 215384.615 for 112 Days

 

[Dave Patton]

You stated " my Answer is INR 215384.615 for 112 Days".

Please show how you calculated this number.

 

[Dave Patton]

T202006b.xlsm
	B	C	D	E	F
1
2	Start	End	Per Month	Total
3	10-Apr-20	18-Aug-20	50,000.00	214,245.01
4
5	10-Apr-20	30-Apr-20	18	26	34,615.38
6	01-May-20	31-May-20	26	26	50,000.00
7	01-Jun-20	30-Jun-20	26	26	50,000.00
8	01-Jul-20	31-Jul-20	26	26	50,000.00
9	01-Aug-20	18-Aug-20	16	27	29,629.63
10			112	131	214,245.01
11
12		Start	Full Months	End	Total
13		34,615.38	150,000.00	29,629.63	214,245.01
14
2a

Cell Formulas
Range	Formula
E3	E3	=NETWORKDAYS.INTL(MAX(B5,EOMONTH(B5,-1)+1),MIN(EOMONTH(B5,0),C5),16)/NETWORKDAYS.INTL(EOMONTH(B5,-1)+1,EOMONTH(C5,0),16)*D3+3*D3+NETWORKDAYS.INTL(MAX(B9,EOMONTH(B9,-1)+1),MIN(EOMONTH(B9,0),C9),16)/NETWORKDAYS.INTL(EOMONTH(B9,-1)+1,EOMONTH(C9,0),16)*D3
D5:D9	D5	=NETWORKDAYS.INTL(MAX(B5,EOMONTH(B5,-1)+1),MIN(EOMONTH(B5,0),C5),16)
E5:E9	E5	=NETWORKDAYS.INTL(EOMONTH(B5,-1)+1,EOMONTH(C5,0),16)
F5:F9	F5	=D5/E5*$D$3
D10:F10	D10	=SUM(D5:D9)
C13	C13	=NETWORKDAYS.INTL(MAX(B5,EOMONTH(B5,-1)+1),MIN(EOMONTH(B5,0),C5),16)/NETWORKDAYS.INTL(EOMONTH(B5,-1)+1,EOMONTH(C5,0),16)*D3
D13	D13	=3*D3
E13	E13	=NETWORKDAYS.INTL(MAX(B9,EOMONTH(B9,-1)+1),MIN(EOMONTH(B9,0),C9),16)/NETWORKDAYS.INTL(EOMONTH(B9,-1)+1,EOMONTH(C9,0),16)*D3
F13	F13	=SUM(C13:E13)

 

[Dave Patton]

T202006b.xlsm
	B	C	D	E	F
2	Start	End	Per Month	Total
3	10-Apr-20	18-Aug-20	50,000.00	214,245.01	214,245.01
4
2a

Cell Formulas
Range	Formula
E3	E3	=NETWORKDAYS.INTL(MAX(B3,EOMONTH(B3,-1)+1),MIN(EOMONTH(B3,0),C3),16)/NETWORKDAYS.INTL(EOMONTH(B3,-1)+1,EOMONTH(B3,0),16)*D3+(DATEDIF(B3-DAY(B3)+1,EOMONTH(C3,0)+1,"m")-2)*D3+NETWORKDAYS.INTL(MIN(C3,EOMONTH(C3,-1)+1),MIN(EOMONTH(C3,0),C3),16)/NETWORKDAYS.INTL(EOMONTH(C3,-1)+1,EOMONTH(C3,0),16)*D3
F3	F3	=NETWORKDAYS.INTL(MAX(B3,EOMONTH(B3,-1)+1),MIN(EOMONTH(B3,0),C5),16)/LOOKUP(B3,NumWD)*D3+(DATEDIF(B3-DAY(B3)+1,EOMONTH(C3,0)+1,"m")-2)*D3+NETWORKDAYS.INTL(MAX(B3,EOMONTH(C3,-1)+1),MIN(EOMONTH(C3,0),C3),16)/LOOKUP(C3,NumWD)*D3

Named Ranges
Name	Refers To	Cells
NumWD	='2a'!$L$1:$M$12	F3

 

[gssachin]

You are an awsome boss...

I provided data in my broken English still u find the way and amaze me. I used formula in "E3" becoz I having start date, end date, and Amount column.... And its works for me.....

Thanks a lot..... ?

 

[Dave Patton]

Thanks for the feedback.

 

[Dave Patton]

2 suggestions that are a little cleaner.
One puts the Lookup table that calculates the workdays for each month into a named array.

T202006b.xlsm
	B	C	D	E	F	G
1
2	Start	End	Per Month		Total	Total
3	10-Apr-20	18-Aug-20	50,000.00		214,245.01	214,245.01
4	15-Jan-20	12-Dec-20	54,000.00		589,076.92	589,076.92
5
2a

Cell Formulas
Range	Formula
F3:F4	F3	=(NETWORKDAYS.INTL(B3,EOMONTH(B3,0),16)/NETWORKDAYS.INTL(B3-DAY(B3)+1,EOMONTH(B3,0),16)+(DATEDIF(B3-DAY(B3)+1,EOMONTH(C3,0)+1,"m")-2)+NETWORKDAYS.INTL(C3-DAY(C3)+1,C3,16)/NETWORKDAYS.INTL(C3-DAY(C3)+1,EOMONTH(C3,0),16))*D3
G3:G4	G3	=(NETWORKDAYS.INTL(B3,EOMONTH(B3,0),16)/LOOKUP(B3,N_WD)+(DATEDIF(B3-DAY(B3)+1,EOMONTH(C3,0)+1,"m")-2)+NETWORKDAYS.INTL(C3-DAY(C3)+1,C3,16)/LOOKUP(C3,N_WD))*D3

N_WD means Number of Work Days for each month
See Name Manager N_WD ={43831,26;43862,25;43891,27;43922,26;43952,26;43983,26;44013,26;44044,27;44075,26;44105,26;44136,26;44166,27}

 

[Dave Patton]

T202006b.xlsm
	B	C	D	E
2	Start	End	Per Month	Total
3	10-Apr-20	18-Aug-20	50,000.00	214,245.01
4	15-Jan-20	12-Dec-20	54,000.00	589,076.92
5	17-Mar-20	24-Apr-20	80,000.00	100,056.98
2a

Cell Formulas
Range	Formula
E3:E5	E3	=(NETWORKDAYS.INTL(B3,EOMONTH(B3,0),16)/LOOKUP(B3,N_WD)+(MONTH(C3)-MONTH(B3)-1)+NETWORKDAYS.INTL(C3-DAY(C3)+1,C3,16)/LOOKUP(C3,N_WD))*D3