Formula for using a date to add qty?


I am not sure if this is possible but what I am looking to do is.

  • I have 125 custom flanges in stock
  • I have a shipment of 100 custom flanges due on the 24/4/23
  • I have used 25 custom flanges in order 01

However, the stock of custom flanges is now 200 which is not true because the shipment hasn't arrived yet. What I am looking for is a formula so the shipment of 100 customs flanges won't be added to the stock qty until 24/4/23 when they are due to arrive.

Could someone please help me with this?


  • BdH
    BdH ✭✭

    In stock management it is common to work with a virtual stock level to ensure a user understands that this is stock that is not yet available. To answer your question:

    Add a cell that contains the "Stock date" = 23/01/23

    You could use a formula like this:

    =125 + IF([Finish date]12 < [Stock date]1; [Qty]12; 0) - [Qty]15

    This checks whether the Finish date of Order has passed. If that has happened than it takes the number into account.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!