How do I do a if / then formula for dates?

M. Wolf

I'm using the following formula to figure out when warranties expire, [Start Up Date]@row+[Labor Warranty Days]@row. This formula works great to figure out when the warranty expires, but a certain customer has a longer warranty which is called out in a different column, how can I make a formula to show if the Labor Warranty's Days is blank, to use the Labor Warranty Days for Customer column? Or is it not possible, I didn't originally setup the SS, so I'm not sure why they have it in separate columns like that, just trying to improve it.

Nick Korna

Hi @M. Wolf,

Is there a separate column listing the customer, or is it just list of the start up dates and then the labor/parts as above (normal/customer specific)?

If the latter, then you can do this like this:

Labor:

=IF([Labor Warranty for Customer]@row <> "", [Start Up Date]@row + [Labor Warranty for Customer]@row, [Start Up Date]@row + [Labor Warrant Days]@row)

Parts:

=IF([Labor Warranty for Customer]@row <> "", [Start Up Date]@row + [Labor Warranty for Customer]@row, [Start Up Date]@row + [Parts Warranty]@row)

Sample output (based on above data):

Hope this helps, but if I've misunderstood something or you've any problems/questions then just post! 🙂

M. Wolf

That works, thank you!

One more question, is there a way to setup the cell to automatically turn red once the warranty expires?

Nick Korna

You can do this with the conditional formatting which is this symbol on the bar:

A rule like this would colour expired labor warranty cells, for example:

As well as this, you could also set up filters to show only active warranties:

This would mean you don't have to look at any rows where the warranty no longer applies (and can remove it to see all data as required, such as for audit purposes).

M. Wolf

@Nick Korna thank you so much for all your help!!

Nick Korna

No problem at all, glad to have helped! 🙂