Split dates on correct months

[Simper]

I'm using excel to manage my B&B activity.
I have a table with check-in and check-out dates.
In a separate sheet I need a function to summarize guest and nights for each month.
Nothing special until dates belongs to same month. The problem I have is when dates are across month. I should correctly divide nights in each month but I don't know how to do it. The file is an example to clarify the requirement. In red the cross month periods.

Thanks for your time.

 

[Hui]

Simper

Firstly, Welcome to the Chandoo.org Forums

For your question
I5: =SUMPRODUCT(($D$5:$D$15<J$4)*($E$5:$E$15>=I$4)) Enter
I6: =SUMPRODUCT(((IF($E$5:$E$15<J$4,$E$5:$E$15,J$4)-IF($D$5:$D$15<=I$4,I$4,$D$5:$D$15))*($D$5:$D$15<J$4)*($E$5:$E$15>=I$4))) Ctrl+Shift+Enter

Then copy across

See attached file:

 

[Simper]

Hi Hui,
thanks for your response, that's exactly what I need. I tried to use the formula and substitute cell range with table names but I have an error on the nights row, I think it's related to the IF that doesn't like names. In the attachment you can have a look.
Thanks

 

[Hui]

I don't like using Table References despite the advantages they offer

I would used named Formula for this if you want to simplify it

In: =Sheet1!$D$5:$D$15
Out: =Sheet1!$E$5:$E$15

Then change I6 to: =SUMPRODUCT(((IF(OUT<K$4,OUT,K$4)-IF(IN<=J$4,J$4,IN))*(IN<K$4)*(OUT>=J$4))) Ctrl+Shift+Enter

If the ranges are going to change you could use a Dynamic Named range like:

In: =OFFSET(Sheet1!$D$5,,,COUNTA(Sheet1!$D$5:$D$10000))
Out: =Offset(In,,1)

 

[Simper]

...hmmm , I tried to use dynamic named range instead of table references but I have the same #VALUE! error. Very frustrating

 

[NARAYANK991]

Hi ,

Your named ranges were both text strings as follows :

="OFFSET(Sheet1!$D$5,,,COUNTA(Sheet1!$D$5:$D$10000))"

="OFFSET(Sheet1!$E$5,,,COUNTA(Sheet1!$E$5:$E$10000))"

Both of them should be without the quote marks , as follows :

=OFFSET(Sheet1!$D$5,,,COUNTA(Sheet1!$D$5:$D$10000))

=OFFSET(Sheet1!$E$5,,,COUNTA(Sheet1!$E$5:$E$10000))

Narayan

 

[Simper]

Hi Narayan,
I created the named ranges from Name manager:

1. If I insert =OFFSET(Sheet1!$D$5,,,COUNTA(Sheet1!$D$5:$D$10000)) I got an error "the forumula you typed contains an error..."
2. If I insert OFFSET(Sheet1!$D$5,,,COUNTA(Sheet1!$D$5:$D$10000)) it accept the value but then is transformed to ="OFFSET(Sheet1!$D$5,,,COUNTA(Sheet1!$D$5:$D$10000))"

Is there another way to do it?

Thanks

 

[Khalid NGO]

Hi Simper,

I just removed the quote marks at start and end of both named formulas, as explained by Narayan Sir.
I am getting result without any error.

Regards,

 

[NARAYANK991]

Hi ,

Since the definition is a formula , it has to start with the = symbol. Without it , Excel will treat what ever is entered as text , and will automatically insert the quote marks around the entered string.

I deleted the named ranges , and recreated them without Excel giving any error message ; can you try once more ?

Narayan

 

[Simper]

Hi,

I tried and uploaded the sequence I used. I'm stuck on that error messagge. Could yuo attach you working version?
Thanks

 

[NARAYANK991]

Hi ,

See these 3 files.

Narayan

 

[Simper]

Kindly could you upload your xlsx file. I would like to open it in my env and investigate why I'm having a different result.
Thanks

 

[NARAYANK991]

Hi ,

See this file.

Narayan