Calculating date Difference into int field

[Roo]

I'm trying to calculate the difference in days between to dates after the second has been changed. I've used a formula found in the forums here, but can't seem to get the value in "daystaken" to update. Is the correct format I should be looking at?

fd.field('Returned').change(function(){
var calcStartTime = parseInt(fd.field('RecordedDate').value().replace(/,/g, ''));
var calcEndTime = parseInt(fd.field('ActualReturn').value().replace(/,/g, ''));
var cTotalTime = cEndTime - cStartTime;
fd.field('daystaken').value(cTotalTime);
});

[Nikita Kurguzov]

Dear Rupert,
The method you are using won't be really good, as it only takes day date in consideration, meaning that it gets difference between one day date and another. This would work, if End Date was 31, and Start Date was 10, for example, but it won't work if End Date was 1 (let's say 1st of December) and Start Date was 28 (let's say 28th of November).

Moreover, it depends heavily on what kind of Culture (way of writing a date) you have set up on your SP site, it might work with one Culture, but won't work with another. Plus, the code most probably fails because variable names are inconsistent (calcStartTime vs. cStartTime, for example)

But here's an example that transfers string of date into actual dates and calculates actual day difference, and this should work around most of the limitations I've just described (though it can still run into some, if the culture on the site doesn't match PC's culture, for instance, but most likely it shouldn't):

Code: Select all

//run when form opens
calculateTimeDifference();

//run when dates change
fd.field('StartDate').change(function(){
    calculateTimeDifference();
});

fd.field('EndDate').change(function(){
    calculateTimeDifference();
});

function calculateTimeDifference(){
    if(fd.field('StartDate').value() && fd.field('EndDate').value()){
        var calcStartTime = new Date(fd.field('StartDate').value());
        var calcEndTime = new Date(fd.field('EndDate').value());
        var timeDiff = Math.abs(calcEndTime.getTime() - calcStartTime.getTime());
        var diffDays = Math.ceil(timeDiff / (1000 * 3600 * 24)); 
        fd.field('Title').value(diffDays);
    }
    else{
        //empty value if dates are not set (might also want to check if end date > start date)
        fd.field('Title').value("");
    }
}

I am also unsure what the Returned field is, so I'm just running the code when dates change, but you can adjust this so it works in your use case, it's just an example that I've tested to work.

[Roo]

When I use this I get a a return of "NaN'?

The field is set to be a numeric field (but in SFD it shows as a text field)

nan.jpg (10.35 KiB) Viewed 1880 times

Rupert

[Nikita Kurguzov]

Dear Rupert,
Sorry, the screenshot quality is very bad, but I assume that this happens when either of the dates is empty, correct? I suspect this is simply caused by this line here:

Code: Select all

fd.field('Title').value("");

I've experimented with theTitle field, so didn't get the error, as it's a text field. Instead, use the following code for a number field:

Code: Select all

calculateTimeDifference();

fd.field('StartDate').change(function(){
    calculateTimeDifference();
});

fd.field('EndDate').change(function(){
    calculateTimeDifference();
});

function calculateTimeDifference(){
    if(fd.field('StartDate').value() && fd.field('EndDate').value()){
        var calcStartTime = new Date(fd.field('StartDate').value());
        var calcEndTime = new Date(fd.field('EndDate').value());
        var timeDiff = Math.abs(calcEndTime.getTime() - calcStartTime.getTime());
        var diffDays = Math.ceil(timeDiff / (1000 * 3600 * 24)); 
        fd.field('Number').value(diffDays);
    }
    else{
        fd.field('Number').value(0);
    }
}

[Roo]

Hmmm still getting the same value returned.

Attached are the "Better" Screenshots.

calcday1.png (79.21 KiB) Viewed 1876 times

calcday2.png (63.21 KiB) Viewed 1876 times

calcday3.png (63.06 KiB) Viewed 1876 times

[Nikita Kurguzov]

Dear Rupert,
This is very strange, as I have exactly the same configuration, but let's test it, I'm sure that we can find the reason. Please, replace the function for now with this:

Code: Select all

function calculateTimeDifference(){
    if(fd.field('StartDate').value() && fd.field('EndDate').value()){
        var calcStartTime = new Date(fd.field('StartDate').value());
        console.log(calcStartTime);
        var calcEndTime = new Date(fd.field('EndDate').value());
        console.log(calcEndTime);
        var timeDiff = Math.abs(calcEndTime.getTime() - calcStartTime.getTime());
        console.log(timeDiff);
        var diffDays = Math.ceil(timeDiff / (1000 * 3600 * 24)); 
        console.log(diffDays);
        fd.field('Number').value(diffDays);
    }
    else{
        fd.field('Number').value(0);
    }
}

Then, open the form, and show me a screenshot of browser's console once you get the error (NaN message).

It might also be caused by the field itself, not sure how you've achieved the effect that it shows up as text on the form, but as number on the form itself, this might be causing some errors. If this is not intended, please, try to delete the field and add it back, perhaps this will help.

[Roo]

Hi Nikita

The same issue appear. I've attached the details of the console. It looks like it doesn't like the date format again? (its in UK format in the library) - I think I've had this issue elsewhere.

console:

dateissue1.png (90.14 KiB) Viewed 1874 times

[Nikita Kurguzov]

Dear Rupert,
As you can see, both dates are returned as invalid. Are you sure that you are using correct Internal Names for dates?

Try running these commands ONE by ONE from console, and send me a screenshot, please (just change internal names to match yours):

Code: Select all

fd.field("StartDate").value();
new Date(fd.field("StartDate").value());
fd.field("EndDate").value();
new Date(fd.field("EndDate").value());

I want to see the following output (or at least what exactly goes wrong on each line):

ConsoleDates.png (13.63 KiB) Viewed 1873 times

P.S. If the issue is actually caused by the date format, which is possible, please, use the following code:

Code: Select all

calculateTimeDifference();

fd.field('StartDate').change(function(){
    calculateTimeDifference();
});

fd.field('EndDate').change(function(){
    calculateTimeDifference();
});

function calculateTimeDifference(){
    if(fd.field('StartDate').value() && fd.field('EndDate').value()){
        var startDayArray = fd.field('StartDate').value().split("/");
        var calcStartTime = new Date(startDayArray[2], startDayArray[1], startDayArray[0]);
        var endDayArray = fd.field('EndDate').value().split("/");
        var calcEndTime = new Date(endDayArray[2], endDayArray[1], endDayArray[0]);
        var timeDiff = Math.abs(calcEndTime.getTime() - calcStartTime.getTime());
        var diffDays = Math.ceil(timeDiff / (1000 * 3600 * 24)); 
        fd.field('Number').value(diffDays);
    }
    else{
        fd.field('Number').value(0);
    }
}

[Roo]

Hi Nikita

As requested here is the info:
fd.field("RecordedDate").value();
"20/11/2018"
new Date(fd.field("RecordedDate").value());
Invalid Date
fd.field("ActualReturn").value();
"30/11/2018"
new Date(fd.field("ActualReturn").value());
Invalid Date

What is interesting - If I change the end date to 1/11/2018 (1st November 2018) it is converting the data to US format:
new Date(fd.field("ActualReturn").value());
Thu Jan 11 2018 00:00:00 GMT+0000 (Greenwich Mean Time

[Roo]

When using the newest function
calculateTimeDifference();

fd.field('StartDate').change(function(){
calculateTimeDifference();
});

fd.field('EndDate').change(function(){
calculateTimeDifference();
});

function calculateTimeDifference(){
if(fd.field('StartDate').value() && fd.field('EndDate').value()){
var startDayArray = fd.field('StartDate').value().split("/");
var calcStartTime = new Date(startDayArray[2], startDayArray[1], startDayArray[0]);
var endDayArray = fd.field('EndDate').value().split("/");
var calcEndTime = new Date(endDayArray[2], endDayArray[1], endDayArray[0]);
var timeDiff = Math.abs(calcEndTime.getTime() - calcStartTime.getTime());
var diffDays = Math.ceil(timeDiff / (1000 * 3600 * 24));
fd.field('Number').value(diffDays);
}
else{
fd.field('Number').value(0);
}
}

There is no returned value in the "Number" field, but I have more errors in the console:

dateerror.png (33.64 KiB) Viewed 1870 times