Cell returns value if another cell fits within a date range

[kellyla88]

Here's a quick summary of the purpose of this formula... My company hires out equipment and we need to keep track of where a piece of equipment was at a given time and who to charge the equipment to for a particular week or month.

Each piece of equipment will have a separate sheet, which will list all of the hire contracts which have been placed on that machine:
Column B: Hirer name
Column C: Contract start date
Column D: Contract expiry date

Then there will be a main sheet (Summary) which will have all equipment listed, and all of the chargeable dates listed (see example below)

Week 1 ||
Week Commencing (B6) | Week Ending (C6) ||
25/11/2013 (B7) | 1/12/2013 (C7) ||

I want to add a Box underneath each piece of equipment on the main sheet that will show who to charge to. For example, underneath Week 1, I want it to show who the hirer is based on whether the Week Commencing to Week Ending dates fit within the contract start and expiry dates.

I have come up with a formula that almost works, but I can only get it to refer to a single cell in the equipment sheet. As soon as I try to get it to refer to all columns, it just automatically comes up with 'not on hire'.

This is my formula: =IF(AND(('Equipment1'!C14<='Summary'!C7),('Equipment1'!D14>='Summary'!B7)),('Equipment'!B14),"Not on hire")

I hope that all makes sense. Any help would be greatly appreciated (my head is spinning!)

[Kaper]

Hi,
Probably there is no answer, because without test date it is not that easy to suggest you a proper formula.

Let me try:

Please Login or Register  to view this content.

This is not a standard formula committed with Enter key. It is array formula. Once you write it in your cell or in a formula stripe, commit it with keys: Ctrl+Shift+Enter (Ctrl+Shift hold, press Enter, release Enter, release other two).

Try it - it shall work. Of course you probably shall go down further than row 20. But not go too far (like row 10000 etc) - it will noticeably slow down Excel.