I have two columns [Lease] and [MOT] whereby I like to have a column displaying message "NOK" or "OK" to make the user act upon it.
I now created two calculated columns [LeaseReminder] and [MOTReminder] with the following formula which basically shows -30 days earlier.
=TEXT([Lease]-30,"dd/MM/yyyy") and =TEXT([MOT]-30,"dd/MM/yyyy")
The "NOK" must be shown when one of the dates either [LeaseReminder] or [MOTReminder] is less than the Today() today's date. Otherwise, if both dates are bigger then today's date then show "OK" message.
I fail to make it to work and being trying to use such combination:
=IF(OR(TEXT(TODAY(),"dd/MM/yyyy")>=[LeaseReminder],TEXT(TODAY(),"dd/MM/yyyy")>=[MOTReminder]), "NOK", "OK")
However, this does not work when both dates are less then today's dates or even when one of the dates is less than today!
The purpose is simply to indicate that one of the dates (or both dates) are about to expire and needs to be looked at. Otherwise, if dates are well ahead then there is no reason for the warning :)