Rules to set date field value to a calculated value

[Charlene]

HELP!! I’m trying to set up rules to set a calculated date field value based on the input of an end date but need some help! I know how to calculate the dates in Excel, but don’t know JS well enough to incorporate into SharePoint! Can someone please help me with this? I added a list of dates that are calculated in Excel for reference. Do you also know how to move the tooltips to the left of the field instead of the right?

End date inputted: 3/31/2018

Note: All dates below should be on business days.

PL Date: This should be at least 2 months before End date and on a Thursday = 1/25/2018
GTM Date: This should be at least 1 month before End date and on a 2nd or 4th Friday = 2/23/2018
Comm1 Date: This should be the next business day after GTM Date = 2/26/2018
Comm2 Date: This should be the next business day after Comm1 Date = 2/27/2018

THANK YOU!

Attachments:

1. 

   dates.png
   

1. PL-and-GTM-dates.xlsx

[Alexander Bautz]

Hi,
I’ll try to help you out here, but I’m struggling a bit with the logic for the “GMT Date”. Is what determines if it should be the 2 or 4 which of the dates that are nearest to one month from the end date?

Moving the tooltips should work with this in the custom css:

.customTooltip{
    float:none!important;
    display:inline;
    margin-right:5px;
}

[Charlene]

Thanks Alexander! Yes, what determines the ‘GTM Date’ is the nearest Friday to to the 1 month before the end date. If that’s too hard to write in, it could be the following 2nd or 4th Friday’s after 1 month before the end date.

Thank you so much!

[Alexander Bautz]

Sorry for the delay. I’ve been busy with other support issues during the weekend and will get back to you later this week with the code snippet.

Best regards,
Alexander

[Charlene]

No problem at all! Thank you so much!

[Alexander Bautz]

Hi,
I have finally found time to write up a function for you. I had to do a bit of thinking – I’ll admit that, and I’m not 100% sure this will work in all scenarios so you will have to test it properly.

Add this code to the Custom JS in DFFS backend:

function dffs_getDate(arg){
    var neg = arg.offset.months < 0 || arg.offset.days < 0, date = new Date(arg.date.valueOf()), targetMonth = date.getMonth()+arg.offset.months;
    // offset day
    if(arg.offset.days !== 0){
        date.setDate(date.getDate()+arg.offset.days);
    }
    // offset month
    if(arg.offset.months !== 0){
        if(neg){
            while(date.getMonth() !== targetMonth){
                date.setDate(date.getDate()-1);
            }
        }else{
            while(date.getMonth() !== targetMonth){
                date.setDate(date.getDate()+1);
            }
        }  
    }
    // nearest
    if(arg.key === "nearest"){
        while(date.getDay() !== arg.targetDay.day){
            if(neg){
                date.setDate(date.getDate()-1);
            }else{
                date.setDate(date.getDate()+1);
            }
        }
    }
    // nth
    if(arg.key === "nth"){
        while(date.getDay() !== arg.targetDay.day){
            if(neg){
                date.setDate(date.getDate()-1);
            }else{
                date.setDate(date.getDate()+1);
            }
        }
        var dateIndexObj = getDateIndexArr(new Date(date.valueOf()),arg.targetDay.day), loopTicker = 0;
        while(jQuery.inArray(String(dateIndexObj[date.toLocaleDateString()]),arg.targetDay.index) < 0){
            loopTicker += 1;
            if(loopTicker > 1000){
                alert("Failed to calculate the date");
                break;
            }
            if(date.getDay() !== arg.targetDay.day){
                date.setDate(date.getDate() - (7 - date.getDay()));
            }else{
                date.setDate(date.getDate() - 7);
            }
        }
    }
    // businessday
    if(arg.key === "businessday"){
        while(date.getDay() === 0 || date.getDay() === 6){
            if(neg){
                date.setDate(date.getDate()-1);
            }else{
                date.setDate(date.getDate()+1);
            }
        }
    }
    return date;
}

function getDateIndexArr(date,targetDay){
    var month = date.getMonth(), dArr = [], dObj = {};
    while(month === date.getMonth()){
        dArr.unshift(new Date(date.valueOf()).toLocaleDateString());
        date.setDate(date.getDate() - 7);
    }
    jQuery.each(dArr,function(i,d){
        dObj[d] = i+1;
    });
    return dObj;
}

Then use this format when calling the function:

// ************ Days are 0-indexed - use these index numbers in "targetDays" ******************
// 0 = Sunday
// 1 = Monday
// 2 = Tuesday
// 3 = Wednesday
// 4 = Thursday
// 5 = Friday
// 6 = Saturday

var format = spjs.dffs.getRegionalSettings().DateFormat;
var start = spjs.dffs.strToDateObj("DateColumn1",format);

// PL Date
var pl_date = dffs_getDate({
    "date":start,
    "key":"nearest",
    "offset":{
        "months":-2,
        "days":0},
    "targetDay":{
        "index":null,
        "day":4
    }
});
console.log("pl_date:"+pl_date);

// GTMDate
var gtm_date = dffs_getDate({
    "date":start,
    "key":"nth",
    "offset":{
        "months":-1,
        "days":0},
    "targetDay":{
        "index":["2","4"],
        "day":5
    }
});
console.log("gtm_date:"+gtm_date);

// Comm1 Date
var comm1_date = dffs_getDate({
    "date":gtm_date,
    "key":"businessday",
    "offset":{
        "months":0,
        "days":1},
    "targetDay":{
        "index":null,
        "day":null
    }
});
console.log("comm1_date:"+comm1_date);

// Comm2 Date
var comm2_date = dffs_getDate({
    "date":comm1_date,
    "key":"businessday",
    "offset":{
        "months":0,
        "days":1},
    "targetDay":{
        "index":null,
        "day":null
    }
});
console.log("comm2_date:"+comm2_date);

Change the name of the date column from “DateColumn1” to whatever your field has as “FieldInternalName”.

To set the date in a datepicker, use this format:

setFieldValue("NameOfDateCol",spjs.utility.dateObjToFormattedString(pl_date,"MM/dd/yyyy")

Let me know how this works out.

Best regards,
Alexander

[Charlene]

Thank you so much Alexander! I tried including the 1st and 2nd code you provided above in Custom JS and changed the name of the DateColumn1 to my End Date.

I was getting an error with the setFieldValue code you have above, so I inserted the following code after console.log instead.

var pl_date2 = spjs.utility.dateObjToFormattedString(pl_date,"MM/dd/yyyy");
setFieldValue("PLDateColumn",pl_date2);

The problem I’m seeing is:
1. The ‘EndDate’ is not being retrieved. It is pulling in today’s date even though I updated the DateColumn1 to the ‘EndDate’. I’d like this function to trigger when the EndDate is not null or updated and when PLDate, GTMDate, Comm1Date, Comm2Date are empty. Is that possible? Do I need to use rules or just include it all in the Custom JS? Perhaps add: if(“start” == “”){setFieldValue…};?

2. The year represented is 1969 vs 2017. How do I fix this?

THANK YOU!

• This reply was modified 7 years ago by Charlene.

[Alexander Bautz]

You can set a rule to trigger on change of the “EndDate” field, and wrap the code in a function so yo can call it from the rule.

If your “EndDate” isn’t pulled in right you should try running these lines in the console (hit F12 > Console):

var format = spjs.dffs.getRegionalSettings().DateFormat;
var start = spjs.dffs.strToDateObj("EndDate",format);
console.log(start);

Is the start logged in the console correctly?

Alexander

[Charlene]

Wrapping the code in a function and calling it from the rule worked beautifully!!!!!! THANK YOU SOOOOOOO MUCH!!!

And thank you so much for your patience with the troubleshooting! I am not very familiar with JavaScript so your notes for the console helped a TON!!

And one thing I wanted to note was that I ended up creating 2 rules because there are 2 end dates I’m working with. When I tried cloning the 1st rule, the 2nd rule didn’t work, but when I created them as new rules, the 2nd rule started working. I’m not sure if that’s a bug with the cloning feature, but wanted to bring it up in case it is.

Thanks again, Alexander!!!! Sending you a few beers for your help!!!

[Alexander Bautz]

I’m glad you got it up and running – and thank you for the beer 🙂

Have a nice weekend,
Alexander

[Chris Diltz]

I’ve been trying to modify this to calculate a due date in business days, but with no luck. Any suggestions?

[Alexander Bautz]

Did you look at the “businessday” key? – it is used in the “comm2_date” function call in the code snippet above.

Alexander

[Charlene]

Hi Alexander! I’ve been noticing the script gets hung up when I try to put in a date in 2019. It works when I put a date in 2018 first, and then change the date to 2019. But if I start with 2019, it gets hung up.

Do you know why that would be and how to fix it?

Thank you!

• This reply was modified 6 years, 4 months ago by Charlene.

[Charlene]

Sorry Alexander, I made a rookie mistake. The problem I was having was with the rule I set up to run the function. I changed the trigger to “is changed” and everything is working beautifully. Thank you again for your work on this! 🙂

[Alexander Bautz]

I’m glad it worked out.

Alexander

[Bryan Waldrop]

Hi Alexander!
What would “add 3 business days to todays date” look like?

Thanks!

[Alexander Bautz]

Try it like this:

var new_date = dffs_getDate({
    "date":new Date(), // = today
    "key":"businessday",
    "offset":{
        "months":0,
        "days":3},
    "targetDay":{
        "index":null,
        "day":null
    }
});
console.log(new_date);

Alexander

[Bryan Waldrop]

Thank you! That works perfectly if today is a Monday or Tuesday. Even Wednesday skips Sat and Sun to generate Monday as the new_date. However, if today is a Thursday or Friday then Monday is also selected as new_date instead of Tue/Wed?

[Alexander Bautz]

Ah – I actually forgot how this was made (based on the original request). The “offset” of three days is actually applied first, and then it finds the first business day after that day.

I have changed the code and added a new offset parameter “businessdays” – see example below code snippet.

function dffs_getDate(arg){
    var neg = arg.offset.months < 0 || arg.offset.days < 0, date = new Date(arg.date.valueOf()), targetMonth = date.getMonth()+arg.offset.months;
    // offset days
    if(arg.offset.days !== 0){
        date.setDate(date.getDate()+arg.offset.days);
    }
    // offset businessdays
    if(arg.offset.businessdays !== undefined && arg.offset.businessdays !== 0){
        var bdCount = 0;
        var found = false;
        while (!found) {
            if(date.getDay() === 0 || date.getDay() === 6){
                date.setDate(date.getDate()+1);
            }else{
                if(bdCount !== arg.offset.businessdays){
                    date.setDate(date.getDate()+1);
                }else{
                    found = true;
                }
                bdCount += 1;
            }
        }
    }
    // offset months
    if(arg.offset.months !== 0){
        if(neg){
            while(date.getMonth() !== targetMonth){
                date.setDate(date.getDate()-1);
            }
        }else{
            while(date.getMonth() !== targetMonth){
                date.setDate(date.getDate()+1);
            }
        }  
    }
    // nearest
    if(arg.key === "nearest"){
        while(date.getDay() !== arg.targetDay.day){
            if(neg){
                date.setDate(date.getDate()-1);
            }else{
                date.setDate(date.getDate()+1);
            }
        }
    }
    // nth
    if(arg.key === "nth"){
        while(date.getDay() !== arg.targetDay.day){
            if(neg){
                date.setDate(date.getDate()-1);
            }else{
                date.setDate(date.getDate()+1);
            }
        }
        var dateIndexObj = getDateIndexArr(new Date(date.valueOf()),arg.targetDay.day), loopTicker = 0;
        while(jQuery.inArray(String(dateIndexObj[date.toLocaleDateString()]),arg.targetDay.index) < 0){
            loopTicker += 1;
            if(loopTicker > 1000){
                alert("Failed to calculate the date");
                break;
            }
            if(date.getDay() !== arg.targetDay.day){
                date.setDate(date.getDate() - (7 - date.getDay()));
            }else{
                date.setDate(date.getDate() - 7);
            }
        }
    }
    // businessday
    if(arg.key === "businessday"){
        while(date.getDay() === 0 || date.getDay() === 6){
            if(neg){
                date.setDate(date.getDate()-1);
            }else{
                date.setDate(date.getDate()+1);
            }
        }
    }
    return date;
}

function getDateIndexArr(date, targetDay) {
    var month = date.getMonth(),
    dArr = [],
    dObj = {};
    while (month === date.getMonth()) {
        dArr.unshift(new Date(date.valueOf()).toLocaleDateString());
        date.setDate(date.getDate() - 7);
    }
    jQuery.each(dArr, function(i, d) {
        dObj[d] = i+1;
    });
    return dObj;
}

Use it like this to offset 3 business days:

var new_date = dffs_getDate({
    "date":new Date(), // = today
    "key":"businessday",
    "offset":{
        "months":0,
        "days":0,
        "businessdays": 3
    },
    "targetDay":{
        "index":null,
        "day":null
    }
});
console.log(new_date);

Let me know how this works out.

Alexander

[Bryan Waldrop]

Thank you sir! This works brilliantly and is something I will be able to reuse in many applications. Piggy Bank utilized!

[Alexander Bautz]

Thanks!
Alexander

[Author]

Posts