BMC Helix Remedyforce - How to Calculate Business Working Hours and Days for an Incident Record

Version 1
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    BMC Helix Remedyforce Service Desk



    QUESTION:

    How to Calculate Working Hours and Days for an Incident Record?


    ANSWER:

    1. Create a Formula FIeld in the Incident Object and called it Working Business Hours
    2. Return Type is Number
    3. Formula value use this:

    [Number of Hours] *((5*FLOOR((DATEVALUE(BMCServiceDesk__closeDateTime__c)-DATE(1900,1,8))/7) + MIN(5, MOD(DATEVALUE(BMCServiceDesk__closeDateTime__c)-DATE(1900,1,8),7)+ MIN(1,24/ [Number of Hours] *(MOD(BMCServiceDesk__closeDateTime__c-DATETIMEVALUE('1900-01-08 12:30:00'), 1))) )) - (5*FLOOR((DATEVALUE(BMCServiceDesk__openDateTime__c + MAX(DATETIMEVALUE(TEXT(DATEVALUE(BMCServiceDesk__openDateTime__c))&" 12:30:00")-BMCServiceDesk__openDateTime__c,0))- DATE(1900,1,8))/7)+ MIN(5, MOD(DATEVALUE(BMCServiceDesk__openDateTime__c+MAX(DATETIMEVALUE(TEXT(DATEVALUE(BMCServiceDesk__openDateTime__c))&" 12:30:00")-BMCServiceDesk__openDateTime__c,0))-DATE(1900,1,8),7)+ MIN(1,24/ [Number of Hours] *(MOD(BMCServiceDesk__openDateTime__c + MAX(DATETIMEVALUE(TEXT(DATEVALUE(BMCServiceDesk__openDateTime__c))&" 12:30:00")-BMCServiceDesk__openDateTime__c,0)-DATETIMEVALUE('1900-01-08 12:30:00'), 1))))) )

    4. Replace:
    [Number of Hours]: Put the the number of Business hour of the company (e.g 10)
    12:30:00: Put the initial time when the shift starts, should be in GMT zone. (e.g 12:30:00 GMT is 8 am CST)

    5. Save Changes

    Note: If you want to calculate Days instead of Hours create a second formula field (with number as return type) and divide above formula Working_Business_Hours__c / [Number of Business Hours]

    Considerations
    -Formula will return working hours from Monday - Friday from the specified start time till the number of business hours
    -Formula does not count weekends.
    -Formula does not count Holidays nor Daylight saving time


     


    Article Number:

    000181882


    Article Type:

    FAQ/Procedural



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles