0

I have a number field which is Average Time calculated based on difference between two date fields , i want to convert this number field into Days Hours Minutes , how can we achieve this.

I have tried creating below formula :

TEXT(
    FLOOR(Average_Time_To_Review__c)/24) & " Days: " &
TEXT(
    FLOOR(
        ((Average_Time_To_Review__c) - FLOOR(Average_Time_To_Review__c)) * 24
    )
) & " Hrs: " &
TEXT(
    ROUND(
            (
                (
                    (
                        MOD(
                            ((Average_Time_To_Review__c) - (FLOOR(Average_Time_To_Review__c))), 
                            (FLOOR(Average_Time_To_Review__c))
                        ) * 24
                    ) 
                    - (
                        FLOOR(((Average_Time_To_Review__c) - (FLOOR(Average_Time_To_Review__c))) * 24)
                    )
                ) * 60
            ),
            0
        )
) & " Mins"

My Input is 1.50
expected out put is 0 Days 1 Hours 30 Minutes
but I got .041666666666666666666666666666666666667 Days: 12 Hrs: 0 Mins

1

The difference between two date/time fields is expressed in hours. It seems like you basically know this, based on your statement of expected input and output. But your formula seems to have been written without really keeping that in mind. Here's the working formula, explanation below:

TEXT(
    FLOOR(Days_to_recruit__c/24)) & " Days: " &
TEXT(
    MOD(
        FLOOR(
            Days_to_recruit__c
        ), 24
    )
) & " Hrs: " & 
TEXT(
    ROUND(
        MOD(Days_to_recruit__c,1) * 60
        , 0
    )
) & " Mins" 

To get the number of whole days, we divide by 24, and take the FLOOR() of that. Yours was close, but was taking the floor before dividing. The fractional hour won't affect this calculation, so we don't need to account for it.

For whole hours, we want the remainder(mod) after we divided by 24. So we lop off the decimal part with FLOOR(), and then get the MOD(n,24) of that. Your section here seems to be thinking the input is in days, not hours, because you're multiplying by 24.

For minutes, we only need to use the decimal part. We get the decimal part of the hour with MOD(n,1), multiply by 60 minutes in an hour, and then ROUND() to 0 decimal places.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.