Where in this formula would I add in within 1000km

Ridley_HK

I use a smartsheet to track my fleet. When something meets this criteria it ticks the box and then an automation is activated.

I was wondering how I would change this so somewhere in the formula the km's were within 1000 or over the RUC amount. This way before it is overdue, it sends the email.

Here is my current formula and an image of my sheet: =IF(OR(AND(NOT(ISBLANK([Service Due Date]@row)), [Service Due Date]@row <= TODAY(14)), (AND(ISBLANK([Service Due KM's]@row), [Service Due KM's]@row < [Odometer Reading]@row))), true)

Paul Newcome

Give this a go...

=IF(OR(AND(NOT(ISBLANK([Service Due Date]@row)), [Service Due Date]@row <= TODAY(14)), (AND(ISBLANK([Service Due KM's]@row), [Service Due KM's]@row - 1000 < [Odometer Reading]@row))), true)

Genevieve P.

Hi @Ridley_HK

I see you marked Paul's response as not answering your question. Can you clarify if you received an error (and what error) or an incorrect result?

Thanks!

Genevieve

Ridley_HK

@Genevieve P. it didn't tick my box

Genevieve P.

Hi @Ridley_HK

It looks like you have contradicting statements for the last part of your formula.

 =IF(OR(AND(NOT(ISBLANK([Service Due Date]@row)), [Service Due Date]@row <= TODAY(14)), (AND(ISBLANK([Service Due KM's]@row), [Service Due KM's]@row < [Odometer Reading]@row))), true)

Here you're looking for two criteria in the AND statement:

• That the Service Due KM's in this row is Blank
• And that it's less than the Odometer Reading

It sounds like you maybe only want to have this when the Service Due KM's is within 1,000 or less than the Odometer Reading, is that correct?

If so, try Paul's formula again, but remove out the first ISBLANK part of your AND statement:

=IF(OR(AND(NOT(ISBLANK([Service Due Date]@row)), [Service Due Date]@row <= TODAY(14)), [Service Due KM's]@row - 1000 < [Odometer Reading]@row), true)

If you meant to say that it's NOT blank, we can adjust your formula like so:

=IF(OR(AND([Service Due Date]@row <> "", [Service Due Date]@row <= TODAY(14)), AND([Service Due KM's]@row <> "", [Service Due KM's]@row - 1000 < [Odometer Reading]@row)), true)

<> "" is the same as saying NOT(ISBLANK()), I just find it easier to read and write as I don't have to keep track of so many parentheses.

Let me know if that helped! If not, it would be useful to have a bullet point list of all the instructions you want to happen.

Cheers,

Genevieve

Paul Newcome

@Genevieve P. Good catch! I was just plugging in the number in relation to the odometer reading.