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

#### kiki4255

##### New Member
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!

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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:
Code:
``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?

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
AB
1Enter Date2/4/2010
2Exit Date11/29/2010
3
4January0
5February24
6March31
7April30
8May31
9June30
10July31
11August31
12September30
13October31
14November28
15December0
Sheet1
Cell Formulas
RangeFormula
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.

Actually I needed to include the proper year for this to work, so this one should work:
Excel Workbook
AB
1Enter Date2/4/2010
2Exit Date11/29/2010
3
4January0
5February24
6March31
7April30
8May31
9June30
10July31
11August31
12September30
13October31
14November28
15December0
Sheet1
Cell Formulas
RangeFormula
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))))

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?

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.

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?

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.

Last edited:
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.

Replies
1
Views
110
Replies
1
Views
110
Replies
2
Views
453
Replies
1
Views
64
Replies
4
Views
80

1,217,438
Messages
6,136,616
Members
450,022
Latest member
Joel1122331

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back