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
pgonzaleznetworkpgonzaleznetwork 

Date/Time formula question

I have found this formula which works as a charm to calculate the number of days/hours/min between to date/time fields. I am trying to understand how the formula works but I am on a dead end. Please help. First, you need to create a formula field (number) that subtracts one date/time field from the other date/time. In my case, this formula field is called Time_to_approve__c

The formula that calculates the days/hour/min looks like this:

IF ( Time_to_approve__c > 0,
TEXT(FLOOR(Time_to_approve__c)) & " days " &
TEXT( FLOOR( 24 * ( Time_to_approve__c - FLOOR(Time_to_approve__c) ))) & " hours " &
TEXT( ROUND(60 * (ROUND( 24 * ( Time_to_approve__c - FLOOR(Time_to_approve__c) ),8) - FLOOR(
ROUND( 24 * ( Time_to_approve__c - FLOOR(Time_to_approve__c) ),8)) ),0)) & " mn "
, "")

The formula field looks like this: 2 days 1 hours 12 mn

 

Right now, the value of Time_to_approve__c is 2 (***again, this is the result of a date/field value being substraced from another date/time field value***)

 

 I understand the first part of the formula :
TEXT(FLOOR(Time_to_approve__c)) & " days " it rounds the number to the nearest integer, which is 2. So this results in " 2 days" which makes sense.

The issue is on the 2nd part of the formula, which calculates the hours. The syntax says:

TEXT( FLOOR( 24 * ( Time_to_approve__c - FLOOR(Time_to_approve__c) )))

This currently returns a value of 1.

FLOOR(Time_to_approve__c) is 2 (I confirmed this with another formula field) and again, Time_to_approve__c is 2. So, (Time_to_approve__c) - FLOOR(Time_to_approve__c) is 0, multiplied by 24 is 0 too, rounded to its nearest integer would return 0 as 0 is also considered a whole number...then, why does this part of the formula returns a value of 1?

Thank you.

Best Answer chosen by Admin (Salesforce Developers) 
Shannon HaleShannon Hale

I would use this formula instead: it's a little easier to read and understand, and it compiles more efficiently (all those ROUND and FLOOR functions in the other one can make it go over the compile size limit).

 

IF( 
  Time_to_approve__c > 0 , 
  TEXT( FLOOR( Time_to_approve__c ) ) & " days " 
    & TEXT( FLOOR( MOD( Time_to_approve__c * 24, 24 ) ) ) & " hours " 
    & TEXT( ROUND( MOD( Time_to_approve__c * 24 * 60, 60 ), 0 ) ) &" minutes", 
  ""
)

 The way this one works is as follows:

 

Time_to_approve = DateTime1 - DateTime2 (returns a value in days)

Number of hours = Time_to_approve * 24

Number of minutes = Number of hours * 60

 

The modulus (MOD) -- or remainder -- of the number of hours divided by 24 is the number of hours not accounted for by days. The modulus of the number of minutes divided by 60 is the number of minutes not accounted for by hours.

 

That said, to answer your original question:

 

(Time_to_approve__c) - FLOOR(Time_to_approve__c), if you don't calculate it as an integer, returns the number of days minus the "day" part of number of days -- so if Time_to_approve__c is equal to, say, 2.0416666, then 2.0416666 - 2 = 0.0416666. That would round down to zero, but in the calculation, it isn't rounded. When you multiply that by 24, you get the number of hours.

 

All Answers

Shannon HaleShannon Hale

I would use this formula instead: it's a little easier to read and understand, and it compiles more efficiently (all those ROUND and FLOOR functions in the other one can make it go over the compile size limit).

 

IF( 
  Time_to_approve__c > 0 , 
  TEXT( FLOOR( Time_to_approve__c ) ) & " days " 
    & TEXT( FLOOR( MOD( Time_to_approve__c * 24, 24 ) ) ) & " hours " 
    & TEXT( ROUND( MOD( Time_to_approve__c * 24 * 60, 60 ), 0 ) ) &" minutes", 
  ""
)

 The way this one works is as follows:

 

Time_to_approve = DateTime1 - DateTime2 (returns a value in days)

Number of hours = Time_to_approve * 24

Number of minutes = Number of hours * 60

 

The modulus (MOD) -- or remainder -- of the number of hours divided by 24 is the number of hours not accounted for by days. The modulus of the number of minutes divided by 60 is the number of minutes not accounted for by hours.

 

That said, to answer your original question:

 

(Time_to_approve__c) - FLOOR(Time_to_approve__c), if you don't calculate it as an integer, returns the number of days minus the "day" part of number of days -- so if Time_to_approve__c is equal to, say, 2.0416666, then 2.0416666 - 2 = 0.0416666. That would round down to zero, but in the calculation, it isn't rounded. When you multiply that by 24, you get the number of hours.

 

This was selected as the best answer
pgonzaleznetworkpgonzaleznetwork

Hi!

 

Thank you so much, makes perfect sense!