Calculating date Difference into int field
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);
});
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
- Posts: 889
- Joined: Mon Jul 03, 2017
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):
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.
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("");
}
}
Cheers
- Nikita Kurguzov
- Posts: 889
- Joined: Mon Jul 03, 2017
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:
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:
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("");
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);
}
}
Cheers
- Nikita Kurguzov
- Posts: 889
- Joined: Mon Jul 03, 2017
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:
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.
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);
}
}
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.
Cheers
- Nikita Kurguzov
- Posts: 889
- Joined: Mon Jul 03, 2017
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):
I want to see the following output (or at least what exactly goes wrong on each line):
P.S. If the issue is actually caused by the date format, which is possible, please, use the following code:
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());
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);
}
}
Cheers
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
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
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:
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:
-
- Information
-
Who is online
Users browsing this forum: No registered users and 11 guests