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!
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

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,937
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,937
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,937
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
10,887
Office Version
  1. 2019
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,878
Latest member
DDhol
Top