Jan 09 2019 11:34 AM
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
Jan 09 2019 12:43 PM
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.
Jan 09 2019 12:58 PM
Yes I have several units of the same type. Attached is a sample of what I have. thank you
Jan 09 2019 01:23 PM
A little clunky, but this should get you started
Jan 09 2019 01:58 PM
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
Jan 09 2019 03:51 PM
Hi, I started this answer earlier, but forgot to click the POST button.
May I suggest a different layout?
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,
Jan 09 2019 05:29 PM - edited Jan 09 2019 05:31 PM
Thank You! This helped me a ton!
Jan 09 2019 05:30 PM
Thank You! With a few tweaks I was able to get it to do exactly what I want!