1

I have formula field-

Hours_Left__c=Budgeted_Hours__c - Billable_Hours__c

Where I get Number value like-

8.70(Hours_Left__c) = 10.00(Budgeted_Hours__c )-1:30(Billable_Hours__c)

Here I want 8:30 in my Hours_Left__c field?

All fields are numbers. I would like to know how can I convert a number column into time format (hh:mm)?

1 Answer 1

2

To do this, you need all of your input to be in the same domain. Here, you're working with input in two different domains, which I'll call decimal and time.

The decimal domain (I'll assume a precision/scale of 2) goes from 0.00 - 0.99 before rolling over to 1.00.

The time domain goes from 0.00 - 0.59 before rolling over to 1.00

Getting the fractional part of your Billable Hours can be done in a formula field by using MOD(Billable_Hours__c, 1)

From there, converting from the time domain to the decimal domain is as simple as multiplying by 10/6. This is fairly basic math (unit conversion), so I won't go over it in detail.

When you're done with your work in the decimal domain, you want to covert back to the time domain by multiplying by 6/10. You only want to do this for the fractional part though.

In the end, your formula should look something like this

FLOOR(
  (Budgeted_Hours__c - FLOOR(Billable_Hours__c) - (MOD(Billable_Hours__c, 1) * 10/6))
)
+ 
(
  MOD((Budgeted_Hours__c - FLOOR(Billable_Hours__c) - (MOD(Billable_Hours__c, 1) * 10/6)), 1) * 6/10
)

There really isn't a way to get around the duplication in the resulting formula here. This is probably a sign that you'd be better off doing this in Apex rather than a formula field...but there it is.

+edit: If your compiled formula size is too large, then either one, or both, of your fields is/are a formula field itself. It doesn't take too much, when referencing formula fields in another formula field , to push yourself over the compiled size limit.

At that point, you'd have 2 options:

  • Work on making your other formula fields more efficient
  • Create a workflow rule to perform a field update, taking the value of Billable_Hours__c or Budgeted_Hours__c, and sticking them into new number fields (that you'd need to create), and using those new number fields in the formula in place of the old, formula fields

Again, I must stress that doing this through Apex is likely a much better solution. You should seriously consider this if it is an available option.

2
  • I got error: Error: Compiled formula is too big to execute (6,157 characters). Maximum size is 5,000 characters
    – user24524
    Commented Oct 9, 2015 at 6:21
  • The compiled formula size for me in one of my Sandboxes comes to 1056, so either your Budgeted_Hours__c is a formula field, or your Billable_Hours__c is (or both). Like I said at the end of my answer, you're probably better off doing this in Apex. I'll update my answer with another suggestion in case Apex isn't an option for you.
    – Derek F
    Commented Oct 9, 2015 at 15:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .