How to insert =IF () - Formula result is a negative

David Bulmer

Hello all,

can someone help me fix this formula so that the result is zero or higher? i.e. no negative results.

=([Delivery to Site]@row - [Warehouse Storage Starts]@row) * 20

The formula calculates cargo unpack date versus delivery date, plus free storage time, then adds storage cost.

I've tried to add a IF statement but keep getting #UNPARSEABLE

Thanks

Kelly Moore

Sorry, I forgot the leading square bracket in Delivery to Site

=IF([Delivery to site]@row - [Warehouse Storage Starts]@row > 0, ([Delivery to site]@row - [Warehouse Storage Starts]@row) * 20, 0)

Kelly Moore

Hey @David Bulmer

=IF(Delivery to Site]@row - [Warehouse Storage Starts]@row>0, ([Delivery to Site]@row - [Warehouse Storage Starts]@row) * 20, 0)

Does this work for you?

Kelly

David Bulmer

Thanks Kelly, but I'm still getting the same error.

Kelly Moore

Sorry, I forgot the leading square bracket in Delivery to Site

=IF([Delivery to site]@row - [Warehouse Storage Starts]@row > 0, ([Delivery to site]@row - [Warehouse Storage Starts]@row) * 20, 0)

Paul Newcome

There are quite a few ways of doing this. My personal take would be:

=MAX(0, ([Delivery to Site]@row - [Warehouse Storage Starts]@row) * 20)

You could also use

=IF([Delivery to Site]@row >= [Warehouse Storage Starts]@row, ([Delivery to Site]@row - [Warehouse Storage Starts]@row) * 20, 0)

David Bulmer

Thank you all for your input. This formula is now working!

=IF([Delivery to Site]@row - [Warehouse Storage Starts]@row > 0, ([Delivery to Site]@row - [Warehouse Storage Starts]@row) * 20, 0)

My next challenge with this formula is; if delivery date to site is blank, use today's date, so that a live calculation of storage fees is displayed. Any ideas for this?

Kelly Moore

Hey David

Does this work for you?

=IF(ISDATE([Delivery to Site]@row), IF([Delivery to Site]@row - [Warehouse Storage Starts]@row > 0, ([Delivery to Site]@row - [Warehouse Storage Starts]@row) * 20, 0), IF(TODAY() - [Warehouse Storage Starts]@row > 0, (TODAY() - [Warehouse Storage Starts]@row) * 20, 0))

Kelly

Paul Newcome

This would be my suggestion:

=IF(IF([Delivery to Site]@row <> "", [Delivery to Site]@row, TODAY()) - [Warehouse Storage Starts]@row > 0, (IF([Delivery to Site]@row <> "", [Delivery to Site]@row, TODAY()) - [Warehouse Storage Starts]@row) * 20, 0)