Count number of day based on entry and leaving date (per month)

[sastoka]

Hi there,

I'm facing an issue and i don't even know if it's possible to fix.


Context:

I'm calculating allowances. The amount is per paid day, so I need to know how many days each employee has been in a country.

While I know the total number of days. it's supposed to be a salary so I need to break it down per month.


Need:

I would like some formula that auto populate the number of days depending on the entry and leaving date.


Difficulties:

Since I work with salary I assume that each month has 30 days (make it easier for calculation) so that's an added difficulty (Maybe)

Also it needs to take into account that when it's about entry, the entry day must be counted as a day in the country.

I know how to do that with a vba but the save location doesn't support macro file.


Example:

I've included a picture (can't poste proper table currently) of the expected result in 3 different situations.

Thanks in advance to those who take the time to read and think about it.

If you need more info let me know. And if i'm not clear, i'm sorry.

Regards,

Sastoka.

PS: I'm using Excel 2013.

 

[oldbrewer]

if salary is $35000 per year and in January 21days in the country amount = 35000/365.25*21

entry date 3/1/16
leaving date 23/1/16
days = 23/1/16 - 3/1/16 + 1


for your first example

=((15/10/16-6/1/16)+1)/365.25 * yearly salary

 

[sastoka]

Hi Oldbrewer,

Thanks for this, but I would like each month to auto update.

Basicaly i want to enter an entry date and a leaving date

and the month below to be calculated.


Thanks.

 

[wideboydixon]

This might work for you:

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H
1	Example 1			Example 2			Example 2
2
3	Entry Date	06/01/2016		Entry Date	14/05/2016		Entry Date	01/02/2016
4	Leaving Date	15/10/2016		Leaving Date	30/09/2016		Leaving Date	01/07/2016
5
6	Month	Number of day in the country		Month	Number of day in the country		Month	Number of day in the country
7	January	25		January	0		January	0
8	February	30		February	0		February	30
9	March	30		March	0		March	30
10	April	30		April	0		April	30
11	May	30		May	17		May	30
12	June	30		June	30		June	30
13	July	30		July	30		July	1
14	August	30		August	30		August	0
15	September	30		September	30		September	0
16	October	15		October	0		October	0
17	November	0		November	0		November	0
18	December	0		December	0		December	0
Sheet1

Cell Formulas
Range	Formula
A7	=DATE(YEAR(B$3),ROWS($7:7),1)
B7	=MAX(MIN(B$4,DATE(YEAR(A7),MONTH(A7),30))-MAX(B$3,A7)+1,0)
D7	=DATE(YEAR(E$3),ROWS($7:7),1)
E7	=MAX(MIN(E$4,DATE(YEAR(D7),MONTH(D7),30))-MAX(E$3,D7)+1,0)
G7	=DATE(YEAR(H$3),ROWS($7:7),1)
H7	=MAX(MIN(H$4,DATE(YEAR(G7),MONTH(G7),30))-MAX(H$3,G7)+1,0)

WBD

 

[RCBricker]

Try this. It checks for leap year and uses the actual last day of a given month

=IF(OR(MONTH($B$1)>MONTH(DATEVALUE($A5&" 1")),MONTH($B$2)<MONTH(DATEVALUE($A5&" 1"))),0,IF(MONTH($B$1)=MONTH(DATEVALUE($A5&" 1")),IF(OR(MOD(YEAR($B$1),400)=0,AND(MOD(YEAR($B$1),4)=0,MOD(YEAR($B$1),100)<>0)),SUM(CHOOSE(MONTH(DATEVALUE($A5&" 1")),31,29,31,30,31,30,31,31,30,31,30,31)-DAY($B$1)),SUM(CHOOSE(MONTH(DATEVALUE($A5&" 1")),31,28,31,30,31,30,31,31,30,31,30,31)-DAY($B$1))),IF(MONTH($B$2)=MONTH(DATEVALUE($A5&" 1")),IF(OR(MOD(YEAR($B$1),400)=0,AND(MOD(YEAR($B$1),4)=0,MOD(YEAR($B$1),100)<>0)),SUM(CHOOSE(MONTH(DATEVALUE($A5&" 1")),31,29,31,30,31,30,31,31,30,31,30,31)-DAY($B$2)),SUM(CHOOSE(MONTH(DATEVALUE($A5&" 1")),31,28,31,30,31,30,31,31,30,31,30,31)-DAY($B$2))),IF(OR(MOD(YEAR($B$1),400)=0,AND(MOD(YEAR($B$1),4)=0,MOD(YEAR($B$1),100)<>0)),CHOOSE(MONTH(DATEVALUE($A5&" 1")),31,29,31,30,31,30,31,31,30,31,30,31,CHOOSE(MONTH(DATEVALUE($A5&" 1")),31,29,31,30,31,30,31,31,30,31,30,31))))))

 

[RCBricker]

Sorry missed the criteria that you assume every month is 30 days

try this: just insert the formula in the cell to the right of the month

=IF(OR(MONTH($B$1) > MONTH(DATEVALUE($A5&" 1")),MONTH($B$2) < MONTH(DATEVALUE($A5&" 1"))),0,IF(MONTH($B$1)=MONTH(DATEVALUE($A5&" 1")),SUM(30-DAY($B$1)),IF(MONTH($B$2)=MONTH(DATEVALUE($A5&" 1")),SUM(30-DAY($B$2)),30)))

 

[sastoka]

@RCBricker,

unfortunately, the formula doesn't work it return, #VALUE, as it can't calculate the formula MONTH.

@wideboydixon,
yours worked like a charm, it did exactl what i asked.

thanks a lot.

Thanks all.