ForceWizard: Formula Hours to Mins and Mins to Hours

I was getting a requirement from the client to calculate Total Hours based on the user input. When I am searching on the google I didn't get a proper result so, I thought of writing this. User will enter the time in any of these format e.g. 1.00, 1.62 so the total hours should be calculated a s 3.00.

1. Convert the user input hours spent to min

  IF( (FLOOR(Hours_Spent__c) = Hours_Spent__c) ,
(Hours_Spent__c*60)
,
(
(
VALUE(
LEFT( TEXT(Hours_Spent__c), FIND('.', TEXT(Hours_Spent__c)))
) * 60
) +
VALUE(
RIGHT( TEXT( Hours_Spent__c * 100 ) , 2)
)
)
)

2. Summarise the above the field and then by using this summarised field calculate the total hours from the summarised min.

IF( (MOD( Total_Hours_Spent_Rollup__c, 60) == 0),
Total_Hours_Spent_Rollup__c/60,
(
VALUE(
LEFT( TEXT(Total_Hours_Spent_Rollup__c / 60), FIND('.', TEXT( Total_Hours_Spent_Rollup__c / 60)))
)+
(MOD( Total_Hours_Spent_Rollup__c, 60) / 100)
)
)

I hope this will help most of them. Please let me know your feedback in comments.

Formula Hours to Mins and Mins to Hours

No comments:

Post a Comment