Home

Formula Help!!

WGCSDT
Occasional 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!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies