Need help sorting data with additional variable of comparing date ranges

Copper Contributor

Hi all,

 

This might be asking too much, but here's what i'm looking for. I have attached my excel grid. 

 

Basically, my goal here is to find out how many of an item I need to order. Everything is working fine, but one thing I'd like it to take into account is the delivery and return date section at the top. The idea behind that is if I have gear with "return date" it means its free starting on that date. If that return date is on (or before) another columns delivery date, that means I can use that same piece of gear and it shouldn't count as an additional item I need to order.

 

in the grid, here's an example:

In Column 9 I have a delivery date of 7/27 and a return date of 7/31 - I need 6 laptops for that space

In Column 4 I have a delivery date of 7/31 and a return date of 8/3 - I need 2 laptops for that space

 

the way its set up now that's telling me I need 8 laptops no matter what, but in reality I only need 6 because the 2 from column 4 I can actually use for column 9.

 

Similarly, if column 9 had a return date of 7/30, that would still be on or before 7/31 so it should only count 6 laptops, but if the return date was 8/1, then I have overlapping needs and it should count 8 laptops.

 

I'm mind boggled how I could make it use those cells and compute all of that. Is there anyone out there that might be able to assist?

 

https://1drv.ms/x/s!AiRsLmWKJwgbhxY-xKKzHJlrCLSj?e=ZIOZe4 

 

Thank you!

 

1 Reply

I tried to edit text a bit for clarity, if anyone has questions please let me know! If someone can assist that would be amazing! there are times I need to add a LOT of columns, and sorting through it all can be a hassle. I'm trying to keep it in a way where I can track by column, but it still tracks the gear dynamically by day.

At one point, I'd love for that grid to account for day use, and spit out another grid on a separate tab that accounts the for the gear by date and gear (date on top, gear like it is) and updates dynamically with what I have in this tab. For now just being able to track what I have in original post would be so helpful! If anyone has ideas / help / better way they think I should do it, please let me know!