web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

Community site session details

Community site session details

Session Id :
Microsoft Dynamics CRM (Archived)

How Many Hours Based on Time/Date Field

(0) ShareShare
ReportReport
Posted on by 10,328 Moderator

So................

I'm actually going to ask a question for once before I go into meltdown!

I want to have Field A (Date & Time) & Field B (Date & Time) these will always be on the same day and I need to work out how many hours have been spent on site - I have been pulling my hair out on the old calculated fields but cant seem to pick up a formula for this one - Does anyone have any ideas? i'm trying as much as I can to stay away from any custom code

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    PS Profile Picture
    23,577 on at

    Hi Mark,

    Go through my blog, it caters the exact requirement:

    prashantshuklacrm.wordpress.com/.../calculating-time-spent-in-minutes-on-an-activity-via-javascript-in-ms-crm-2015

    if my post answered your question, please mark it as verified

  • Suggested answer
    Mahadeo Matre Profile Picture
    17,021 on at

    Mark,

    You cannot achieve this until you use some sort of custom code.. either JavaScript or Custom workflow or plugin.

    Calculated fields supports only functionality like adddays, substractdays, but not have date difference..

    Here are more on calculated field

    technet.microsoft.com/.../dn832103.aspx

    You can use code given by Prashant, or create custom workflow activity which will calculate difference and update entity.

    Also Check

    wfdatetimeutilities.codeplex.com/documentation

    Hope this will help..

  • NatrajY Profile Picture
    3,040 on at

    This cannot be done using either Business Rules or Calculated fields. You'll can do this via Javascript, Plugin or Workflow depending on your requirements. BTW, I submitted a Connect request for adding this feature in calculated fields sometime back.

    connect.microsoft.com/.../add-datediff-capability-to-calculated-fields

    Please vote up if you think it would be a nice feature to have.

  • Suggested answer
    PS Profile Picture
    23,577 on at

    Hi Mark,

    the blog URL, i have provided contains the complete javascript code to achieve this, so, you just need to copy & paste & change the field names :)

  • TheMarkChristie Profile Picture
    10,328 Moderator on at

    That looks like the solution - Is there a way to do this in hours rather than minutes?

  • Verified answer
    Aileen Gusni Profile Picture
    44,524 on at

    Hi Mark,

    What entity you have?

    If your entity is Activity, basically CRM has the Start Date and End Date together with the duration field to calculate the time duration spent between them.

    Example here:

    stardate2.png

    You can use that concept. So for activity entity, if you are using that, if can fit using those fields, you are no need to create new field or javascript anymore.

    Btw, if not, then you can create the field as Whole Number and Duration to get hours displayed

    stardate3.png

    http://missdynamicscrm.blogspot.com/2014/05/crm-20112013-know-more-about-duration.html

     in back end it is in minutes, but in UI, in reality, it can show in hour and day as well.

     

    See example here:

    stardate4.png

    Hope this helps you.

    Thank you.

  • Suggested answer
    Community Member Profile Picture
    on at

    Building on Aisling's solution, if you are using an activity entity:

    You already have a [Planned] Duration field, that is calculated as the difference between scheduledstart and scheduledend. You could use Actual Duration field instead, that uses actualstart and actualend. Notice that these are not usually on the activity form, but are filled in based on user marking activity as completed. Use whichever one makes most sense, and label them unambiguously for the user.

    1) Add two new fields:

    Hours Long - decimal, 10 places, not searchable or audited

    Hours Duration - decimal, 2 places (or however many you think you need, noting that 1/4 hour needs two, but in some industries it is common to use 6 minute intervals to use only a single decimal)

    You do not have to put these fields on the form. You could choose to put hours duration as well as or instead of the usual duration field, but make it read-only.

    2) Create a real-time workflow, triggered when the activity record is created, or when duration changes.

    Steps:

    Check duration has a value

    Update record: Hours long = duration

    Update record again: Hours long - multiply by 0.0166666667 (this is why you need 10 decimal places)

    Update record again: Hours = Hours long (this will do rounding up/down to nearest number in last place, not truncation)

    Activate your workflow and test

    3) Use your Hours duration field in reports, charts etc.

    PS: You cannot do the above as a CRM 2015 calculated field, because duration fields are not supported.

  • yleclerc Profile Picture
    1,549 on at

    Hi Adam,

    Are you saying that the Actual Duration field should populate automatically?

    I just want it to display the difference between actualend and actualstart when the activity is closed.

    Right now the Actual Duration needs to be populated by hand.

    Thanks!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans