Forum Discussion

WGCSDT's avatar
WGCSDT
Copper Contributor
Jan 09, 2019

Formula Help!!

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 contrac...
excel
Formulas and Functions
IngeborgHawighorst's avatar
IngeborgHawighorst
MVP
Jan 09, 2019

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.

WGCSDT's avatar
WGCSDT
Copper Contributor
Jan 09, 2019

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

    • IngeborgHawighorst's avatar
      IngeborgHawighorst
      MVP
      Jan 09, 2019

      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, 

      • WGCSDT's avatar
        WGCSDT
        Copper Contributor
        Jan 09, 2019

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

    • WGCSDT's avatar
      WGCSDT
      Copper Contributor
      Jan 09, 2019

      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