Compare two fields in SharePoing using Calculated Column

Question

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!

See the image below:

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 :)

Answer 1

Seems your logic should be [LeaseReminder]>=TODAY() AND [MOTReminder]>=TODAY() then OK, else NOK according to your description.

While there is a issue when use calculate column with TODAY(), that's calculate column won't update automatically.

Try to use JSLink for this requirement.

Sample script(insert script editor webpart to your list form and insert the script to script editor web part)

<script type="text/javascript">
        (function () {
            'use strict';

            var CustomReminder = {};

            /**
             * Initialization
             */
            function init() {

                CustomReminder.Templates = {};

                CustomReminder.Templates.Fields = {
                    'Reminder': {
                        'View': customDTDisplay
                    }
                };

                // Register the custom template
                SPClientTemplates.TemplateManager.RegisterTemplateOverrides(CustomReminder);
            }
            function customDTDisplay(ctx) {                
                //from the context get the current item and it's value
                if (ctx != null && ctx.CurrentItem != null) {
                    var LeaseReminder = ctx.CurrentItem["LeaseReminder"];
                    //JavaScript use MM/DD/YYYY format by default
                    var dateLeaseParts = LeaseReminder.split("/");
                    var dateLeaseObject = new Date(dateLeaseParts[2], dateLeaseParts[1] - 1, dateLeaseParts[0]);
                    var dtLease = new Date(dateLeaseObject);


                    var MOTReminder = ctx.CurrentItem["MOTReminder"];
                    //JavaScript use MM/DD/YYYY format by default
                    var dateMOTParts = MOTReminder.split("/");
                    var dateMOTObject = new Date(dateMOTParts[2], dateMOTParts[1] - 1, dateMOTParts[0]);
                    var dtMOT = new Date(dateMOTObject);

                    var today = new Date();
                    if (dtLease >= today && dtMOT >= today) {
                        return "OK";
                    } else {
                        return "NOK";
                    }                    
                }               
            }
            function getFormattedDate(date) {
                var year = date.getFullYear();
                /// Add 1 because JavaScript months start at 0
                var month = (1 + date.getMonth()).toString();
                month = month.length > 1 ? month : '0' + month;
                var day = date.getDate().toString();
                day = day.length > 1 ? day : '0' + day;
                return month + '/' + day + '/' + year;
            }

            // Run our intiialization
            init();

        })();
    </script>