If duration is 0 leave cell blank

Caroline Dunnell

I have a duration column which shows the duration of time an order took. I have copied this formula to all cells in the column however some are for new orders not placed yet and if this returns ZERO/0, I want this to be blank.

So far I have this =sum(Receipted in SICON date]@row-[DeliveryDate]@row) - which works out how long an order takes.

But I want it to be blank if its a zero.

Can anyone help please?

Jason Tarpinian

You can use the ISBLANK to check if Delivery Date is input yet to return a date, else do your formula:

=IF(ISBLANK(DeliveryDate@row), "", SUM([Receipted in SICON date]@row - DeliveryDate@row))

Jason Tarpinian

You can use the ISBLANK to check if Delivery Date is input yet to return a date, else do your formula:

=IF(ISBLANK(DeliveryDate@row), "", SUM([Receipted in SICON date]@row - DeliveryDate@row))

Caroline Dunnell

Thanks so much for this, I have tried and its coming up unparsable.

I have set up a duration column which works out the amount of days between, but again still unparsable. This is what I did:

=IF(ISBLANK(Duration@row), " ", [Duration])

Jason Tarpinian

You are missing the @cell syntax at the end of the duration column.

=IF(ISBLANK(Duration@row), " ", [Duration]@row)

Caroline Dunnell

Thanks Jason - this worked, I am still learning the format of the formula so thank you for your help.

However it's still coming up with zero and I need it to be blank with no number in there at all. So I need it to say "if it's zero, then leave it blank".

Any other tips?

Jason Tarpinian

No problem! Are you just referencing the "Duration" column now? If the ISBLANK isn't getting you what you need, try to wrap it in an IF statement to change 0 to blank, something like:

=IF(Duration@row=0, "", Duration@row)

Basically translates to, show me the duration, unless it's 0, show blank. We can adjust the initial formula I helped with too to show the same thing:

=IF(SUM([Receipted in SICON date]@row - DeliveryDate@row) = 0, "", SUM([Receipted in SICON date]@row - DeliveryDate@row))

Caroline Dunnell

Thank you so much Jason, this top one worked a treat so thank you again