Calculate no of months

SOLVED

Hi

i have 2 date fields and want to be able to calculate the no months between the 2 dates - does any one have any sample code of ideas of how to achieve this?

Top Replies

  • Hi !

    You're right! While the code works in the console itself, it tends to spark an error when executed onscreen.

    Since there's a bit too much to type to explain the "why" behind the code not working…

Parents
  • 0
    SUGGESTED

    Hi, !

    I've read through what you're trying to accomplish, and I'll see if I can provide some insight into what you're trying to accomplish.

    I understand there are three fields:

    • Start Date (oppo_date1)
    • End Date (oppo_date2)
    • Calculated number of months (oppo_numberOfMonths)

    Utilizing an OnChange script attached to the date fields, you'd like to have the third field populate a value when there are dates present in the Start/End dates respectively.

    So long as all fields are editable and NOT read only, the below code has been tested and verified to work. Please ensure you replace the field names in the below code with the system's actual field names :

    var startDate = new Date(document.getElementById('oppo_date1').value);
    var endDate = new Date(document.getElementById('oppo_date2').value);
    if(startDate > 0 && endDate > 0){
    	var diffTime = Math.abs(endDate - startDate);
    	var diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24)); 
    	var diffMonths = Math.floor(diffDays / 30)
    	oppo_numberOfMonths.value = diffMonths;
    	}
    	else{
    		console.log("ERROR: Missing parameters. Unable to calculate date. Check your date fields and ensure a value is present in both oppo_date1 and oppo_date2.");
    	}

    If either of the date fields are ReadOnly, you would need to change the first two lines to read...

    // if field is not ReadOnly
    var startDate = new Date(document.getElementById('oppo_date1').value);
    
    // if field is ReadOnly
    var startDate = new Date(document.getElementById('_Dataoppo_date1').innerText);

    I hope that helps! Let me know if you have any questions regarding the above code snippets/examples.

    Best Regards,
    Basil Malik, CRM Practice Leader
    Blytheco
    [email protected]
    #transformingCompanies

  • 0 in reply to Basil Malik

    Hi Basil

    thanks for the reply and code - really appreciate it

    ive added the below code to the onchange script boxes for both my date fields but the months field is not calculating:

    var startDate = new Date(document.getElementById('oppo_mcstartdate').value);
    var endDate = new Date(document.getElementById('oppo_renewaldate').value);
    if(startDate > 0 && endDate > 0){
    var diffTime = Math.abs(endDate - startDate);
    var diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
    var diffMonths = Math.floor(diffDays / 30)
    oppo_months.value = diffMonths;
    }
    else{
    console.log("ERROR: Missing parameters. Unable to calculate date. Check your date fields and ensure a value is present in both oppo_date1 and oppo_date2.");
    }

    when i enter the start and end date the months field is just showing as blank -  could you please advise?

  • 0 in reply to SIQ

    This is why I tend to look at SQL more the same above as a trigger on the opportunity table would be 

    CREATE TRIGGER Opportunity_RecordUpdates
    ON Opportunity 
    AFTER Insert, Delete

    AS
    DECLARE @OppoID INT
    SELECT @OppoID = oppo_opportunityid FROM Inserted

    BEGIN
    UPDATE Opportunity 
    SET oppo_months = DateDiff(Month, oppo_mcstartdate, oppo_renewaldate)
    WHERE oppo_opportunityid = @OppoID 
    END

    As the calculation is done after they have clicked on Save, you would have to make both date field mandatory in CRM as it's too late to stop the user by this time. (Though you could put something else in as a message if you wanted to allow users to continue) 

    That is the advantage of TableScripts 

  • 0 in reply to SIQ
    SUGGESTED

    Hi, !

    I see where I went wrong in my sample code - I left off a semicolon in one of the lines, which causes the code to error and not perform as expected. Please see your below, modified code.

    var startDate = new Date(document.getElementById('oppo_mcstartdate').value);
    var endDate = new Date(document.getElementById('oppo_renewaldate').value);
    if(startDate > 0 && endDate > 0){
        var diffTime = Math.abs(endDate - startDate);
        var diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
        var diffMonths = Math.floor(diffDays / 30);
        oppo_months.value = diffMonths;
    }
    else{
        console.log("ERROR: Missing parameters. Unable to calculate date. Check your date fields and ensure a value is present in both oppo_date1 and oppo_date2.");
    }

    When testing the code, ensure that you can see errors reported by your browser. In Google Chrome, you can hit the F12 key to observe errors. Implement the above code on both fields, and test it with the ability to see errors.when they occur. You will also be able to see the error message from the "console.log()" response.

    Let me know if that fixes it.

    I agree with  that the SQL trigger would be a great solution so long as you're not trying to perform dynamic calculations on the screen. The code provided would be great to calculate values from a database level. The code provided above allows the customization to function dynamically onscreen. Benefits to both methods depending on what you're looking to accomplish.

    Best Regards,


    Basil Malik, CRM Practice Leader
    Blytheco
    [email protected]
    #transformingCompanies

  • 0 in reply to Basil Malik

    Hi Basil

    tried that but still cant get it to work, checked in F12 and cant see any errors 

Reply Children