We need your help for the CRM system in calculating days between 2 date fields. In our specific case we need this to calculate interest days via our custom module "Payments". For this we were already in contact with you and your development department and received a script. Unfortunately, we were not able to solve the problem completely. We looked a little further into the problem and found out the following:
As long as a number between 1 - 12 is entered in the input date field in the place of DD (days), the script recognizes this number as MM (month) and swaps the format of the date from DD.MM.YYYY to MM.DD.YYY.
If the number in the input date field in the place of DD is a number above 12, the script recognizes the number as DD and leaves the format of the date as DD.MM.YYYY. Therefore, we assume that we need an additional condition that looks something like this: IF DD > 12 → toString("DD.MM.YYYY"); The problem is that when we save and run the script everything works fine but when we run it in the Payments module, the above problem occurs and consequently dates are calculated incorrectly.
Example:
Interest start_service: 02.09.2022
First payment quarter: 28.09.2022
Instead of 28 days gap between the two dates, the script calculates 231 days.
//Calculation of the interest days for the service fee
startDate = interest start_service;
endDate = tostring(FirstPaymentQuarter, "dd.MM.yyyy");
if(input.startDate.getday() < 13)
{
startDate = tostring(startDate, "dd.MM.yyyy");
}
info startDate;
StartDay = startDate.getDay();
StartMonth = startDate.getMonth();
StartYear = startDate.getYear();
EndDay = endDate.getDay();
EndMonth = endDate.getMonth();
EndYear = endDate.getYear();
info "StartDay ::" StartDay ;
info "EndDay ::" EndDay ;
info "StartMonth ::" StartMonth ;
info "EndMonth ::" EndMonth ;
info "StartYear ::" StartYear ;
info "EndYear ::" EndYear ;
if(StartMonth == 2 && startDate.getDay() == 30 || startDate.getDay() == 31)
{
startDay = 30;
}
if(startDay == 30 && EndDay == 31 || EndDay == 31)
{
EndDay = 30;
}
resultserrvicegebuehr = (EndYear - StartYear) * 360 + (EndMonth - StartMonth) * 30 + EndDay - StartDay + 1;
info resultserrvicegebuehr;
paymentupdate = zoho.crm.updateRecord("Payments",Payment_ID,{"interestdays_servicefee":resultserrvicegebuehr});
info paymentupdate;
//Calculate the interest days for the cofin
startDatecofin = interest start_cofin;
endDatecofin = tostring(FirstPaymentQuarter, "dd.MM.yyyy");
if(input.startDatecofin.getday() < 13)
{
startDatecofin = tostring(startDatecofin, "dd.MM.yyyy");
}
info startDatecofin;
StartDaycofin = startDatecofin.getDay();
StartMonthcofin = startDatecofin.getMonth();
StartYearcofin = startDatecofin.getYear();
EndDaycofin = endDatecofin.getDay();
EndMonthcofin = endDatecofin.getMonth();
EndYearcofin = endDatecofin.getYear();
info "StartDaycofin ::" StartDaycofin;
info "EndDaycofin ::" EndDaycofin;
info "StartMonthcofin ::" StartMonthcofin;
info "EndMonthcofin ::" EndMonthcofin;
info "StartYearcofin ::" StartYearcofin;
info "EndYearcofin ::" EndYearcofin;
if(startMonthcofin == 2 && startDatecofin.getDay() == 30 || startDatecofin.getDay() == 31)
{
startDaycofin = 30;
}
if(startDaycofin == 30 && endDaycofin == 31 || endDaycofin == 31)
{
EndDaycofin = 30;
}
resultcofin = (EndYearcofin - StartYearcofin) * 360 + (EndMonthcofin - StartMonthcofin) * 30 + EndDaycofin - StartDaycofin + 1;
info resultcofin;
paymentupdate = zoho.crm.updateRecord("Payments",Payment_ID,{"InterestDays_Cofin":resultcofin});
//Calculation of the interest days for the service fee
startDate = interest start_service;
endDate = tostring(FirstPaymentQuarter, "dd.MM.yyyy");
if(input.startDate.getday() < 13)
{
startDate = tostring(startDate, "dd.MM.yyyy");
}
info startDate;
StartDay = startDate.getDay();
StartMonth = startDate.getMonth();
StartYear = startDate.getYear();
EndDay = endDate.getDay();
EndMonth = endDate.getMonth();
EndYear = endDate.getYear();
info "StartDay ::" StartDay ;
info "EndDay ::" EndDay ;
info "StartMonth ::" StartMonth ;
info "EndMonth ::" EndMonth ;
info "StartYear ::" StartYear ;
info "EndYear ::" EndYear ;
if(StartMonth == 2 && startDate.getDay() == 30 || startDate.getDay() == 31)
{
startDay = 30;
}
if(startDay == 30 && EndDay == 31 || EndDay == 31)
{
EndDay = 30;
}
resultserrvicegebuehr = (EndYear - StartYear) * 360 + (EndMonth - StartMonth) * 30 + EndDay - StartDay + 1;
info resultserrvicegebuehr;
paymentupdate = zoho.crm.updateRecord("Payments",Payment_ID,{"interestdays_servicefee":resultserrvicegebuehr});
info paymentupdate;
//Calculate the interest days for the cofin
startDatecofin = interest start_cofin;
endDatecofin = tostring(FirstPaymentQuarter, "dd.MM.yyyy");
if(input.startDatecofin.getday() < 13)
{
startDatecofin = tostring(startDatecofin, "dd.MM.yyyy");
}
info startDatecofin;
StartDaycofin = startDatecofin.getDay();
StartMonthcofin = startDatecofin.getMonth();
StartYearcofin = startDatecofin.getYear();
EndDaycofin = endDatecofin.getDay();
EndMonthcofin = endDatecofin.getMonth();
EndYearcofin = endDatecofin.getYear();
info "StartDaycofin ::" StartDaycofin;
info "EndDaycofin ::" EndDaycofin;
info "StartMonthcofin ::" StartMonthcofin;
info "EndMonthcofin ::" EndMonthcofin;
info "StartYearcofin ::" StartYearcofin;
info "EndYearcofin ::" EndYearcofin;
if(startMonthcofin == 2 && startDatecofin.getDay() == 30 || startDatecofin.getDay() == 31)
{
startDaycofin = 30;
}
if(startDaycofin == 30 && endDaycofin == 31 || endDaycofin == 31)
{
EndDaycofin = 30;
}
resultcofin = (EndYearcofin - StartYearcofin) * 360 + (EndMonthcofin - StartMonthcofin) * 30 + EndDaycofin - StartDaycofin + 1;
info resultcofin;
paymentupdate = zoho.crm.updateRecord("Payments",Payment_ID,{"InterestDays_Cofin":resultcofin});