Formula Help!!

Copper Contributor

Good afternoon. I am somewhat knowledgeable in excel but I am having trouble writing a formula.  I have a rental company with a large quantity of units along with several types of units. I do contracts with events that may require 20-100 or more units and I am trying to write a formula that will tell me what I have available for a given date range so I don't overbook and can't seem to get one to work.  If it matters, the start day and end days aren't always the same day of the week, so these may be anywhere from 1 - 30 day contracts. Thanks in advance for any help!

Darren

8 Replies

Hello,

 

that question is really hard to answer without seeing how your data is organised. Do you have several units of the same model? How is the data stored?

 

Can you post a sample file with just a few items that helps us understand your data? Just a few types of units and a few rentals in the same setup as your original data would be helpful.

Yes I have several units of the same type. Attached is a sample of what I have. thank you

A little clunky, but this should get you started

THANK YOU! This is what I am looking for. However it will only do a single date. I apologize, I don't think I was clear but due to overlapping events is there anyway to make it check a date range? For instance if I am looking at booking an event from 7/15/2019 - 7/29/2019?  Thanks

Now you can check 33 days

Hi, I started this answer earlier, but forgot to click the POST button.

 

May I suggest a different layout?

 

availability.png

 

The formula in cel V3 is

 

=V$1-SUMPRODUCT(--($B$3:$B$12<$U3),--($C$3:$C$12>$U3),D$3:D$12)

 

Copy across and down. Then enter the start date in U3 and in the next rows use formulas to increment by one day per row. Copy down as many rows as you need and make sure to adjust to the source data in columns C to M

 

cheers, 

Thank You! This helped me a ton!

Thank You! With a few tweaks I was able to get it to do exactly what I want!