function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Debbie DockeryDebbie Dockery 

convert number field to text field days, hours and minutes

I have a formula number field to calculate the business hours between 2 date/time fields.  I need to break this down to days, hours, and minutes.  I'm being told that this may require a trigger.  Can someone help me?  
Debbie DockeryDebbie Dockery
Here's my current formula:
ROUND( 9 * ( 
( 5 * FLOOR( ( DATEVALUE( Pending_Verification_Date_Stamp__c ) - DATE( 1900, 1, 8) ) / 7) + 
MIN(5, 
MOD( DATEVALUE( Pending_Verification_Date_Stamp__c ) - DATE( 1900, 1, 8), 7) + 
MIN( 1, 24 / 9 * ( MOD( Pending_Verification_Date_Stamp__c - DATETIMEVALUE( '1900-01-08 12:00:00' ), 1 ) ) ) 
) 
) 
- 
( 5 * FLOOR( ( DATEVALUE( CreatedDate ) - DATE( 1900, 1, 8) ) / 7) + 
MIN( 5, 
MOD( DATEVALUE( CreatedDate ) - DATE( 1996, 1, 1), 7 ) + 
MIN( 1, 24 / 9 * ( MOD( CreatedDate - DATETIMEVALUE( '1900-01-08 12:00:00' ), 1) ) ) 
) 
) 
), 
0 )
Ajay K DubediAjay K Dubedi
Hi Debbie,

Please see the below link & Formula :

Sample Date Formulas

https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=5

Finding the Number of Business Hours Between Two Date/Times
The formula for finding business hours between two Date/Time values expands on the formula for finding elapsed business days. It works on the same principle of using a reference Date/Time, in this case, 1/8/1900 at 16:00 GMT (9 a.m. PDT), and then finding your Dates’ respective distances from that reference. The formula rounds the value it finds to the nearest hour and assumes an 8–hour, 9 a.m. – 5 p.m. work day.

You can change the eights in the formula to account for a longer or shorter work day. If you live in a different time zone or your workday doesn’t start at 9:00 a.m., change the reference time to the start of your workday in GMT. See A Note About Date/Time and Time Zones for more information. 
ROUND( 8 * (
   ( 5 * FLOOR( ( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8) ) / 7) +
    MIN(5, 
     MOD( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8), 7) +
     MIN( 1, 24 / 8 * ( MOD( date/time_1 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) )
    ) 
   )
 -
   ( 5 * FLOOR( ( DATEVALUE( date/time_2 ) - DATE( 1900, 1, 8) ) / 7) +
     MIN( 5,
      MOD( DATEVALUE( date/time_2 ) - DATE( 1996, 1, 1), 7 ) +
      MIN( 1, 24 / 8 * ( MOD( date/time_2 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) )
    )
   ) 
  ), 
0 )
Please mark as best answer if it helps you.

Thank You 
Ajay Dubedi
Debbie DockeryDebbie Dockery
Thank you Ajay.  I used the forumla above which works great.  Is there a way to break the results of the formula into days, hours, and minutes.  For example based on 9 hours per day 8 - 5 EST,  26 hours = 2 days, 8 hours and 0 minutes?
Ajay K DubediAjay K Dubedi
Hi Debbie,
Change the formula to the below :

ROUND( 9 * (
   ( 5 * FLOOR( ( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8) ) / 7) +
    MIN(5, 
     MOD( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8), 7) +
     MIN( 1, 24 / 8 * ( MOD( date/time_1 - DATETIMEVALUE( '1900-01-08 12:00:00' ), 1 ) ) )
    ) 
   )
 -
   ( 5 * FLOOR( ( DATEVALUE( date/time_2 ) - DATE( 1900, 1, 8) ) / 7) +
     MIN( 5,
      MOD( DATEVALUE( date/time_2 ) - DATE( 1996, 1, 1), 7 ) +
      MIN( 1, 24 / 8 * ( MOD( date/time_2 - DATETIMEVALUE( '1900-01-08 12:00:00' ), 1) ) )
    )
   ) 
  ), 
0 )

I have highlighted the changes in Bold
changed 8 to 9 in the first line of formula
and changed the DATETIMEVALUE( '1900-01-08 16:00:00' ) to DATETIMEVALUE( '1900-01-08 12:00:00' )
as DateTime values are stored in GMT, you will have to add the difference of the hours.
8:00 am EST is 12:00 PM in GMT (I have assumed its 4 hr difference between)

Please mark as best answer if it helps you.

Thank You 
Ajay Dubedi
Debbie DockeryDebbie Dockery
Thank you Ajay.  It works!
Debbie DockeryDebbie Dockery
Thank you. Is there a way to do this formula that is not rounding? Debbie Dockery Director, Compliance & Operations | INTRADIEM 404.557.2503 The information contained in this email is intended for the individual or entity above. If you are not the intended recipient, please do not read, copy, use, forward or disclose this communication to others; also, please notify the sender by replying to this message, and then delete this message from your system. Thank you.
Rogerio ManesRogerio Manes
Ajay, could you please show me how the first formula you sent on August 17, 2018 would look like for a company that uses business hours between 7am - 7pm CT? Thank you!