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

Version 1

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

PRODUCT:

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

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