Sumif month matches and days

[forginganewone]

Hi friends!!
I need help with another thing, so basically I have to calculate salaries monthly and have three columns in data 1) Start date for employee 2) End date for employee 3) Salary.

But it should be able to calculate salary for number of days in that month for example in row 1, start date is 16 Nov. So for Nov it should calculate 1000/30*14 since only the employee will work for only 14 days in November.

Secondly It should consider if end date and if end date is blank then it should also calculate

|+|Start Date|End Date|Salary|
|:-|:-|:-|:-|
|1|16-11-21|30-Dec-21| 1,000 |
|2|16-11-21|30-Nov-21| 1,000 |
|3|16-11-21|15-Dec-21| 1,000 |
|4|16-11-21| | 1,000 |

Sample Results

|+|Nov|Dec|
|:-|:-|:-|
|1| 467 | 1,000 |
|2| 467 | - |
|3| 467 | 500 |
|4| 467 | 1,000 |

Note* I require to sum for whole Nov and Dec for salaries falling in the month just did row on row in sample result for clarity.

 

[Sufiyan97]

But it should be able to calculate salary for number of days in that month for example in row 1, start date is 16 Nov. So for Nov it should calculate 1000/30*14 since only the employee will work for only 14 days in November.

Why it's 14?
16 to 30 November is 15 days

 

[forginganewone]

Why it's 14?
16 to 30 November is 15 days

You are absolutely right! it is 15days.

 

[Sufiyan97]

Secondly It should consider if end date and if end date is blank then it should also calculate

If end date is blank then for how many days salary will be calculated?

 

[forginganewone]

If end date is blank then for how many days salary will be calculated?

Complete month but depending on start date for example if Nov 16 and we are calculating for November then 15 days but for Dec with blank end date for 30 days.

 

[Sufiyan97]

I am still not getting what to do is end date is blank

but Try this if it works with end date

Book2
	A	B	C	D	E
1	Names	Start Date	End Date	Monthly	Payable
2	Name1	11/16/2021	11/30/2021	1000	500
3	Name2	11/16/2021	11/30/2021	1000	500
4	Name3	11/16/2021	12/15/2021	1000	968
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=ROUND(SUMIFS(D2:D2,B2:B2,">="&B2,C2:C2,"<="&C2)/DAY(EOMONTH(C2,0)*1)*(C2-B2+1),0)

 

[forginganewone]

Thank you for your effort but as per the *Note in my post, I want to calculate it complete for the month and not row by Row.
For example on a new sheet there will only be months mentioned and total salaries falling in those months will be shown in a single cell