Please help with counting days within months

[slan]

Hi!

First of all Im using Excel in another language, Im quite new at this, and I dont know what to search for in the forums (

Im having two columns with One date when someone moved into an apartment, and the other one when they moved out (or still havent)

Moving in Moving out
2012-11-27 2013-01-14
2012-11-27 2013-01-14
2012-12-12 2013-01-04
2013-01-18 2013-03-26
2013-01-18 2013-03-26
2013-02-04 Not Yet
2013-02-10 2013-02-10
2013-02-18 2013-03-01
2013-02-18 2013-03-01
2013-02-26 Not Yet
2013-03-12 2013-03-25
2013-03-28 Not Yet
2013-03-28 Not Yet
2013-03-28 Not Yet

I need to sum up, how many nights were rented out in Jan, Feb Mar... etc

I dont really know what formula to look for, or if this is something that needs to be done in macro or vba even. Maybe Excel has a way to help me with this.


Thank you SO much in advance, I really apprechiate it!

[BB1972]

slan, welcome to the forum.

Assuming your data is in Columns A & B, in C2 enter: =B2-A2

and copy down as far as required.

Hope this helps.

[daddylonglegs]

I think the requirement is to show the number of days in each month, not just the total days, so if you have start date in A2 and end date in B2 (or "Not Yet") and the first of each month listed in D1 across then try this formula in D2 copied across and down

=MAX(MIN(EOMONTH(D$1,0),IF($B2="Not Yet",TODAY(),$B2)-1)+1-MAX(D$1,$A2),0)

For "Not Yet" entries the end date used will be today - see attached

That doesn't count the end date (like hotel stays) so if you want to count that remove the -1, i.e. use

=MAX(MIN(EOMONTH(D$1,0),IF($B2="Not Yet",TODAY(),$B2))+1-MAX(D$1,$A2),0)

[slan]

Wow, that was really fast!
Thanks alot for your help. The provided example helped really much!

All I need is the total days (lets say its a kind of hotel) for each month.
(see attachment)


Laying up a table like yours and add it together looks like a good way to do it.
Is that how you should do it? (since I have no use for the table other than adding them up).

I think I got it now, is it common to make a table like this and then "hide" it somewhere and only use the data youre looking for?

THANK YOU!!!!

attachment: TEST5.xlsx

edit: (working in excel 2010 right now) thats probably why the code was abit different in terms of use of comma and semicolon

[daddylonglegs]

I think that is a good way to go, you might be able to do it without the table but the calculations will get a lot more complex so I don't recommend that approach.

The comma/semi-colon issue is because of regional settings, my region (UK) uses comma separators but Sweden uses semi-colons

[slan]

Another question regarding same matter.

Lets says I want a graph that shows How many people have stayed each day? (2013-01-01 should be 3 according to the provided example above and 2013-01-15 should show 0.)
In my mind I have to check every specific date to see if it is between the range of the stay on every single column and add it together?
I really have no idea where to go with this one.

A hint or a solution would be wonderful.