2

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: enter image description here

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

2

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>
  • I don't how the JS script updates the [Reminder] field but it works :) hah cool stuff if you know JS. So, if I had to eliminate the JS , how else would my requirement fulfilled ? If the Today() does not work, is there no other way to show a message when the MOTReminder has passed the date ? – Bucki Dec 12 '18 at 7:36
  • Or maybe, another column [Today] with todays date in it and then calculate =IF(OR([Today]<[MOTReminder], [Today]<[LeaseReminder]) , "NOK, "OK") ... would this not work either? – Bucki Dec 12 '18 at 7:39
  • 1
    If you don't use JS, you could create workflow which runs daily to update the field value, or schedule PowerShell script. – Lee_MSFT Dec 12 '18 at 7:49
  • Ah ok in other words, there is no other way OutOfTheBox !! I just tried my theory by creating another column [TodaysDate] and using =Today() + then comparing if([MOTReminder]>TodaysDate, "OK", "NOK") but does not work .... so – Bucki Dec 12 '18 at 7:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.