Calculations with months and years

Hello @acka

You should use the DATETIMESUM operation in the “Date Time Operations” module (https://cff.dwbooster.com/documentation#datetime-module).

Assuming the fieldname1 is a date field. Now you want to sum five months and get the result as a text with date format. In this case, you must use two operations, the DATETIMESUM and GETDATETIMESTRING string as follows:

GETDATETIMESTRING(DATETIMESUM(fieldname1, 'dd/mm/yyyy', 5, 'm'), 'dd/mm/yyyy')

Or for summing 3 years:

GETDATETIMESTRING(DATETIMESUM(fieldname1, 'dd/mm/yyyy', 3, 'y'), 'dd/mm/yyyy')

Or for summing 3 years and 5 months:

GETDATETIMESTRING(DATETIMESUM(DATETIMESUM(fieldname1, 'dd/mm/yyyy', 3, 'y'), 'dd/mm/yyyy', 5, 'm'), 'dd/mm/yyyy')

Best regards.

Thank you very much for your quick reply, your solution works like a charm.

I have another question: if the calculated date is a saturday or sunday, then not this day but the next monday should be displayed. Is this possible?

Hello @acka

Yes, of course, you can use the WEEKDAY operation and check if the returned value is 1 (Sunday) or 7 (Saturday). If the result is 1, you must sum one day to the date and two days if the result is seven.

So, continuing with the previous example, summing 5 months to the fieldname1 field.

(function(){
    var new_date = DATETIMESUM(fieldname1, 'dd/mm/yyyy', 5, 'm'),
        day_of_week = WEEKDAY(new_date, 'dd/mm/yyyy');
    
    if(day_of_week == 1) new_date = DATETIMESUM(new_date, 'dd/mm/yyyy', 1, 'd');
    if(day_of_week == 2) new_date = DATETIMESUM(new_date, 'dd/mm/yyyy', 2, 'd');

    return GETDATETIMESTRING(new_date, 'dd/mm/yyyy');
})()

Best regards.

Thank you very much! There was a typo in the code but with your accompanying explanation I was able to fix it by my own (instead of “if(day_of_week == 2)” in the 6th line it must be “if(day_of_week == 7)“.

Another question: is it possible to identify not only saturdays and sundays, but in addition public holidays? So if the calculated date is a public holiday, then the next working day should be displayed.

Hello @acka,

Yes, my apologies for the typo.

The logic would be the same, for example:

if(GETDATETIMESTRING(new_date, 'dd/mm/yyyy') == '04/07/'+YEAR(new_date)) new_date = DATETIMESUM(new_date, 'dd/mm/yyyy', 1, 'd');

I’m sorry, but the support does not cover the implementation of the users’ projects (forms or formulas). If you need someone that implement your project, it would be a pleasure to offer you a custom coding service from my private website: Click Here

Best regards.

Thanks, I’ll send you an email.