Dates Matching - multiples overlaps.

[flat4raanana]

Hello any potential savior.

I believe that this types of requests needs VBA - since its look pretty demanding. If this can be done with normal excel, I good too.
If you make this work - it will be a mini miracle for me.
I would like really appreciate since my excel skills are growing - to provide me an excel attached with the solution.

I need for a real estate the following.

I have the the month when tenants moved into a flat [Tenants Months]

I want to see which dates overlap and how many per month.
[to take the month from [Tenants Months] and see which dates from the dates in the others tabs OVERLAP with any month and - and then so state how many days are overlapping - this can be more than 1 month.

My desired output, for which I will be eternally grateful is shown in the file I am attaching.
I hope my worked example - done manually, for which I need to perform for 1000s will help..

Million thanks - once again - this will be a dream if it can happen.

Jon.

[PCI]

When is mentioned

Are there overlapping dates
[column B to any date in the Tabs]

is the search to be done for all tabs or only the 3 tabs :
1. Prior Obligations and 2. Rest Days and 3. Travel Dates

[PCI]

Is travel dates can exist several times per month per tenant ?
e.g. tenant = A travel from 2 June 2017 to 3 June 2017 AND from 6 June 2017 to 10 June 2017 ???

[PCI]

See how next UDF can fit into your need for the first check:

Are there overlapping dates
[column B to any date in the Tabs]

In file attached see column "K"

Please Login or Register  to view this content.

[flat4raanana]

Hi
I was super busy and sorry for not responding sooner. It should be done for all tabs. I have 3 now - but there maybe more later.
Does that answer your question.
If you need more details, please let me know.
Thank you for responding and helping me.

[flat4raanana]

Yes - this can happen. - Many travel in the same month.

Based on my replied - do you need to amend the excel to be accurate.

Thank you

[PCI]

do you need to amend the excel to be accurate.

Yes of course

[flat4raanana]

I tried and I do not see how the overlapping days per month are population.
I tried adding the macro - that did not work.

[PCI]

See attached a remake of the UdF "OverlappingExist" test is done in all sheets except "Tenants Months"
Some questions:
Sheet "Tenants Months"
Column "E" test must be done with sheet "1. Prior Obligations" is it ??
Column "G" test must be done with sheet "2. Rest Days" is it ??
Column "I" test must be done with sheet "3. Travel Dates" is it ??

[flat4raanana]

Hi
I still do not see how the data on the tabs E, G and I are automatically updated - that is the need.
David

[PCI]

how the data on the tabs E, G and I are automatically updated

No they are not yet updated.
Only column "K" which must fit to column "D"

For column E, G, I is needed answer from previous thread

Sheet "Tenants Months"

Column "E" test must be done with sheet "1. Prior Obligations" is it ??
Column "G" test must be done with sheet "2. Rest Days" is it ??
Column "I" test must be done with sheet "3. Travel Dates" is it ??

[flat4raanana]

Column "E" test must be done with sheet "1. Prior Obligations" is it ?? Yes
Column "G" test must be done with sheet "2. Rest Days" is it ?? Yes
Column "I" test must be done with sheet "3. Travel Dates" is it ?? Yes

I hope I have answered.

[PCI]

See next code and file attached.
Compare your data with functions' results:
Column E vs F
Column G vs H
Column I vs J

Column D vs K

Results are not really the same but I don't see why functions'value should be wrong
Please comment

Please Login or Register  to view this content.