Calculating length of stay in a month, when have start date and exit date

[kiki4255]

Hello.

I have run into a bit of an issue in excel. I have an enter date and an exit date for a person and need to calculate their length of stay for each month.

For example:
Enter Date: 2/4/2010
Exit Date: 11/29/2010

I would need the length of stay for each month:
February would be : 24
March: 31
April: 30
May: 31
June: 30
July: 31
August: 31
September: 30
October: 31
November: 28

I want excel to figure that out for me, using the start date and exit date, but I am not sure how to do that.

I would appriciate your help with this one. Thanks!

 

[Ron Coderre]

A1: 4-Feb-2010
A2: 29-Nov-2010
and
A3:A13 containing Beginning of Month dates
01-Feb-2010
01-Mar-2010
etc
01-Dec-2010

To match your posted results for the sample dates:

B3: =MAX(MIN($A$2-1,EOMONTH(A3,0))-MAX(EOMONTH(A3,-1),$A$1),0)

Copy that formula down through B13

Is that something you can work with?

 

[schielrn]

Welcome to the Board!

This formula could probably be cleaned up a little, but it works, so I didn't loook into it any further:

Excel Workbook
	A	B
1	Enter Date	2/4/2010
2	Exit Date	11/29/2010
3
4	January	0
5	February	24
6	March	31
7	April	30
8	May	31
9	June	30
10	July	31
11	August	31
12	September	30
13	October	31
14	November	28
15	December	0
Sheet1

Cell Formulas
Range	Formula
B4	=IF(EOMONTH((A4&" 1, 2010")*1,0)< $B$1,0,IF(AND((A4&" 1, 2010")*1< $B$2,EOMONTH((A4&" 1, 2010")*1,0) > $B$2),$B$2-(A4&" 1, 2010")*1,IF(EOMONTH((A4&" 1, 2010")*1,0) > $B$2,0,MIN(DAY(EOMONTH((A4&" 1, 2010")*1,0)),EOMONTH((A4&" 1, 2010")*1,0)-$B$1))))

You need to have the Analysis toolpak installed for EOMONTh to work, let me know if you need help with that as it is already on your computer, but may not be active. Or we can come up with a formula to get around that.

Hope that helps.

 

[schielrn]

Actually I needed to include the proper year for this to work, so this one should work:

Excel Workbook
	A	B
1	Enter Date	2/4/2010
2	Exit Date	11/29/2010
3
4	January	0
5	February	24
6	March	31
7	April	30
8	May	31
9	June	30
10	July	31
11	August	31
12	September	30
13	October	31
14	November	28
15	December	0
Sheet1

Cell Formulas
Range	Formula
B4	=IF(EOMONTH((A4&" 1, "&YEAR($B$1))*1,0)< $B$1,0,IF(AND((A4&" 1, "&YEAR($B$1))*1< $B$2,EOMONTH((A4&" 1, "&YEAR($B$1))*1,0) > $B$2),$B$2-(A4&" 1, "&YEAR($B$1))*1,IF(EOMONTH((A4&" 1, "&YEAR($B$1))*1,0) > $B$2,0,MIN(DAY(EOMONTH((A4&" 1, "&YEAR($B$1))*1,0)),EOMONTH((A4&" 1, "&YEAR($B$1))*1,0)-$B$1))))

 

[kiki4255]

Ron,
This worked great, except for when the enter date and exit date were in the same month. For example if the enter date was 3/4/2011 and the exit date was 3/29/2011 the calculation was off. Is there an easy fix for this problem?

 

[schielrn]

Just use an if statement to test for that:

=if(and(month($A$2)=month($A$1),month($A$1)=month(A3)),$A$2-$A$1,MAX(MIN($A$2-1,EOMONTH(A3,0))-MAX(EOMONTH(A3,-1),$A$1),0))

Hope that helps.

 

[kiki4255]

For some reason when some of the calculations are off by one, ,specifically when the start date was in January, the January calculations appear to be off for some by one?

 

[T. Valko]

For some reason when some of the calculations are off by one, ,specifically when the start date was in January, the January calculations appear to be off for some by one?

[EDIT] I assume you want to count both the start date and end date.

Try this...

A1 = start date
A2 = end date

A4:A? = 1st of the month dates like:

1/1/2010
2/1/2010
3/1/2010
etc
etc

Enter this formula in B4:

=MAX(0,MIN(A4+32-DAY(A4+32),A$2)-MAX(A4,A$1)+1)

Copy down as needed.

 

[jasonb75]

For some reason when some of the calculations are off by one, ,specifically when the start date was in January, the January calculations appear to be off for some by one?

That doesn't really tell us much, please provide some example start and end dates, with the expected results, where Ron's formula is giving a different result.

Ron,
This worked great, except for when the enter date and exit date were in the same month. For example if the enter date was 3/4/2011 and the exit date was 3/29/2011 the calculation was off. Is there an easy fix for this problem?

For this one Ron's formula returns 24 days, your previous examples appear to count days between, but not including the start and end date, which would make this the correct result.