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

kiki4255

New Member
Joined
Mar 21, 2011
Messages
3
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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
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?
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,941
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.
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,941
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))))
 

kiki4255

New Member
Joined
Mar 21, 2011
Messages
3

ADVERTISEMENT

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

Well-known Member
Joined
Apr 4, 2007
Messages
6,941
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

New Member
Joined
Mar 21, 2011
Messages
3

ADVERTISEMENT

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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,601
Messages
5,838,281
Members
430,536
Latest member
Manoj Gaidhankar

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?

Disable AdBlock

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
Top