Time Based Formulas – Converting a Number to Hours and Minutes

Time Based formulas can get complicated so I like to have some base formulas that I work with. Here’s a good example of one of the base formulas you can use to build on top of. This formula uses the FLOOR() function to convert a number field that has a decimal in it to Hours and Minutes.

TEXT(
FLOOR( Time_Number__c )
)
+ ” Hours”
+ ” and ”
+
TEXT(
(
(
Time_Number__c – (FLOOR( Time_Number__c ))/1)*60
)
)
+ ” Minutes”

Let’s discuss what FLOOR() does for just a second – https://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions_a_h.htm&language=en_US#FLOOR

It essentially returns the whole number of a number field.

Let’s break down our formula.

TEXT(
FLOOR( Time_Number__c )
)

** Returns Text of the Whole Number. (i.e. 12.5 will return 12)

+ ” Hours”
+ ” and ”
+

* Adds in some text. Take note of the spaces added in to make it read well.

TEXT(
(
(
Time_Number__c – (FLOOR( Time_Number__c ))/1)*60
)
)

** Takes the Number and subtracts it from the whole number. This returns just the decimal portion of the Number which is then divided by 1. This returns a percent of the decimal portion of the number and then multiplies this by 60 to return the number of minutes. For Example, this 12.5 converts to 12 Hours and 30 Minutes.

2016-04-25_1022