You need to sign in to do that

Don't have an account?

# 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

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.

Shannon 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).

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 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).

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.

pgonzaleznetwork

Hi!

Thank you so much, makes perfect sense!