Excel formula for multiple data

[LCA_]

Hi everyone,

This is my first post in this forum, so I'll start by thanking everyone in advance for your help!

So basically I have an excel sheet that I keep adding data and I have some statistic following it. I have almost all of it automatic but I'm stuck in two points and I can't solve it. So here is the issue:

We are a short term rental company and we add our clients info in order to keep our statistic of how many days in a month are sold and how much each month provided.
The info we provide is we have the check in date, check out date, the full amount paid and the daily rate.
By hand I'm seeing how many nights per month and how much. But obviously some people arrive at the end of the month and stay until next month. The check out day does not count as a stayed night since they don't sleep there.

So I basically need two formulas. One to calculate how many nights in the month - consider that some guests arrive in one month and leave in the other and that the check-out date does not count as a night.

The other formula to calculate how much money in a month - consider what is said above with the days and that we already have a daily rate.

Thanks in advance!
Luís

[samba_ravi]

Hi welcome to the forum

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[LCA_]

Thank you very much Siva!

So I'm sending a workbook that I did just for here with examples of data

So you can see that, for instance, on line 2, Mr X does not count for January statistics, because he left on the 1st of january. So nights and amount are all part of december

But Mr Y arrived on the 29 of January and left on the 3rd of Feb. So he only had 2 nights in Feb.

As you can see the statistics are being done by hand.

Thanks in advance!
Forum excel.png

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[LCA_]

Thanks for the info.

So I'm sending now the excel file.

Thanks in advance

[samba_ravi]

Change b13:b15 as 1/12/2017,1/1/2018 & 1/2/2018 and use below array formula (confirm with Shift+Ctrl+Enter)
c3=SUM(IF(($A$2:$A$4<=EOMONTH($B13,0))*($B$2:$B$4>=$B13),IF($B$2:$B$4>=EOMONTH($B13,0),EOMONTH($B13,0)+1,$B$2:$B$4)-IF($A$2:$A$4>=$B13,$A$2:$A$4,$B13),0))

d3=SUM(IF(($A$2:$A$4<=EOMONTH($B13,0))*($B$2:$B$4>=$B13),IF($B$2:$B$4>=EOMONTH($B13,0),EOMONTH($B13,0)+1,$B$2:$B$4)-IF($A$2:$A$4>=$B13,$A$2:$A$4,$B13),0)*(F$2:F$4))
see the attached file

[LCA_]

Perfect, thanks!

But for some reason if I touch the cell to see the formula it screws the values.. Is it because of the language?

[samba_ravi]

I think the formula is an array formula so that it happened. do the following
Select the cell or stay in the cell
press F2
Press Shift+Ctrl+Enter at a time
now you will get the result

[LCA_]

Perfect,nflsales, that way works perfectly!

It's just a smal thing, no big deal,but is it possible to bypass the formating of the months? I mean, Keep December, for instance instead of 01/12/2017?

[samba_ravi]

Right click the mouse
formatcells > numbers > custom > enter or type
MMMM
under Type: > OK
now 01/12/2017 will looks like December

[LCA_]

Perfect! You're the best!

Thank you very much!

[LCA_]

Hi,

Just used the formulas in the actual excel and I'm getting one problem. In october it's adding one more day. I have a person leaving on the 31st. Could it be that?

[samba_ravi]

=SUM(IF(($A$2:$A$4<=EOMONTH($B13,0))*($B$2:$B$4>=$B13),IF($B$2:$B$4>=EOMONTH($B13,0),EOMONTH($B13,0)+1,$B$2:$B$4)-IF($A$2:$A$4>=$B13,$A$2:$A$4,$B13),0))

Remove the Highlighted "=" signed in your formula like
=SUM(IF(($A$2:$A$4<=EOMONTH($B13,0))*($B$2:$B$4>=$B13),IF($B$2:$B$4>EOMONTH($B13,0),EOMONTH($B13,0)+1,$B$2:$B$4)-IF($A$2:$A$4>=$B13,$A$2:$A$4,$B13),0))

[LCA_]

worked perfectly! Once again, thank you!