how to calculate difference in working days between two date fields in CRM Online 2015

(0) Share

Report

Posted on by Franco Prussian

405

Hi all, I am using CRM Online 2015 and would like to calculate the difference in days between two date fields (start date and end date). The TechNet article located here suggests I can use the DIFFINDAYS function. However, the list of functions I have includes all the Add functions (e.g., ADDHOURS, ADDMONTHS) and Subtract functions (e.g., SUBTRACTHOURS), but none of the DIFF functions appear in the menu. If I type it in by hand, it says that syntax is not available. Am I doing something wrong?

Secondly, my real preference is to calculate the number of WORKING days between two dates. In Excel, there is a function for this called NETWORKDAYS, which will exclude weekends for example. Is that option available somewhere with CRM?

Thanks, Franco

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (0)

Suggested answer

Hamzeh Zawahreh 3,674 on at

Like (0)

Report

hello,

according to your first case you maybe need updates.

-----------------------------------------------------------------

in second case read these link on how to schedule holidays:

blogs.c5insight.com/.../how-to-set-work-hours-and-business-closures-in-microsoft-dynamics-crm

and

inogic.com/.../calendars-holiday-and-customer-service-scheduling-in-microsoft-dynamics-crm-2013-sp1

and see this to see how we can add days to specific days including working days:

https://community.dynamics.com/crm/b/hayersdynamicscrm/archive/2014/06/20/dynamics-crm-how-to-add-working-days-by-calculating-business-closure-and-weekends

but if where you i will use custom entity that store the worked days, and use plugin to calculate the diff between tow dates in the entity and store it in third field.

Was this reply helpful? Yes No
Suggested answer

Rajkumar Rajaraman on at

Like (0)

Report

Hi Franco,

Is this what you are looking for?:

blog.customereffective.com/.../duration-calculations-in-crm-with-little-or-no-customizations.html

Hope this helps.

Regards,

R.Rajkumar

"Please mark my answer as verified if you found it helpful"

Was this reply helpful? Yes No
Aileen Gusni 44,524 on at

Like (0)

Report

Hi Franco,

Yes, this is not possible to calculate two dates diff.

Last Time I suggested people asking the same question by creating a duration field (manual calculation), or if it is possible to make it as Activity entity, then CRM has this OOB function.

See my answer before:

community.dynamics.com/.../154329

community.dynamics.com/.../151561

Hope this helps you!

Thank you.

Was this reply helpful? Yes No
Suggested answer

Joe Gill on at

Like (0)

Report

Hi Franco,

You could use North52 Formula Manager - checkout the Workflow Calculate Working Days video

http://www.north52.com/business-process-activities/formula-manager/

Its free for 5 formulas

hth

Joe

Was this reply helpful? Yes No
Soma Shekar 461 on at

Like (0)

Report

Hi Franco,

Hopefully the following link will help you

http://www.crmsoftwareblog.com/2014/04/business-working-hours-calculator-for-microsoft-dynamics-crm/

Was this reply helpful? Yes No
Suggested answer

Hamzeh Zawahreh 3,674 on at

Like (0)

Report

hi,

use this js function:

var calculateDaysBetween = function () {

var datetime1= Xrm.Page.getAttribute("new_FieldName1").getValue().getDay();

var datetime2= Xrm.Page.getAttribute("new_FieldName2").getValue().getDay();

// The number of milliseconds in one day

var oneDay = 1000 * 60 * 60 * 24;

// Convert both dates to milliseconds

var date1Ms = datetime1.getTime();

var date2Ms = datetime2.getTime();

// Calculate the difference in milliseconds

var differenceMs = Math.abs(date1Ms - date2Ms);

// Convert back to days and return

return Math.round(differenceMs / oneDay);

};

Was this reply helpful? Yes No
StaceyKCRM 80 on at

Like (0)

Report

Can you tell me how it returns the value to the new field that is capturing the number of days between the 2 dates? I see where the 2 date fields are being identified in your solution, but do not see where it shows the field where the days between is being captured. Please advise, as I am really stuck and would appreciate your input.

Was this reply helpful? Yes No
VMarques 200 on at

Like (0)

Report

I suggest using this workflow from Codeplex. It was created by Jason Lattimer CRM MVP

crmdatetimeworkflowutilities.codeplex.com

Custom workflow actions that deal with date and time values in Dynamics CRM 2011, 2013, & 2015

Add Business Days

Add Days

Add Hours

Add Minutes

Add Months

Add Years

Date Difference Days

Date Difference Hours

Date Difference Minutes

Date Difference Months

Date Difference Years

Get Day Number Of Week

Get Day Number Of Year

Get Day Of Week

Get Quarter Number Of Year

Get Week Number Of Year

Get Month Start/End Dates

Get Quarter Start/End Dates

Get Week Start/End Dates

Get Year Start/End Dates

Is Between

Is Business Day

Is Same Day

Relative Time String

ToDateTime

To UTC String

Was this reply helpful? Yes No
StaceyKCRM 80 on at

Like (0)

Report

Thank you for your input, I have used Jason's solutions before, and they are fantastic! In this case I ended up taking a different route to accomplish what I needed. Thank you for taking the time to reply!

Was this reply helpful? Yes No
VMarques 200 on at

Like (0)

Report

How did you end up accomplishing what you needed...JScript?

Was this reply helpful? Yes No