Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
cldavecldave 

Adding up Time fields formatted in text

Hi ,

I have 4 fields containg text values of time it took to complete an application

Form_Completion_Time_Application_Form_P1__c
Form_Completion_Time_Application_Form_P2__c
Form_Completion_Time_Application_Form_P3__c
Form_Completion_Time_Application_Form_P4__c

and the data these fields return are "Consistant enough". Here are all possible  Scenarios
3 min. 41 sec.
13 min. 31 sec
12 min. 1 sec
1 min. 9 sec.
28 sec.
9 sec.

I would like to be able to add up all of those 4 fields to get a total, and not sure how to go about it. I tried many things, Substitute formula being the closest I got to it, but still not good enough

The total could either be in total seconds, mm:ss or whatever idea any of you can help me with 


Thank you in advance :)
Best Answer chosen by cldave
YuchenYuchen
You can try the following formula:

TEXT(IF(CONTAINS(Time1__c, "min"), VALUE(LEFT(Time1__c, Find("min", Time1__c)-2))*60 + VALUE(TRIM(MID(Time1__c, Find("min", Time1__c)+5, 2))), VALUE(SUBSTITUTE(Time1__c, " sec.","")))
+IF(CONTAINS(Time2__c, "min"), VALUE(LEFT(Time2__c, Find("min", Time2__c)-2))*60 + VALUE(TRIM(MID(Time2__c, Find("min", Time2__c)+5, 2))), VALUE(SUBSTITUTE(Time2__c, " sec.","")))
+IF(CONTAINS(Time3__c, "min"), VALUE(LEFT(Time3__c, Find("min", Time3__c)-2))*60 + VALUE(TRIM(MID(Time3__c, Find("min", Time3__c)+5, 2))), VALUE(SUBSTITUTE(Time3__c, " sec.","")))
+IF(CONTAINS(Time4__c, "min"), VALUE(LEFT(Time4__c, Find("min", Time4__c)-2))*60 + VALUE(TRIM(MID(Time4__c, Find("min", Time4__c)+5, 2))), VALUE(SUBSTITUTE(Time4__c, " sec.",""))))+" secs"

You may want to replace Time1__c, Time2__c, Time3__c, Time4__c with the API Name of your Custom Fields.

The formula will return the total number of seconds for the four fields.

Hopefully it helps. Thanks.

All Answers

ShotShot
Something like that: 
String s = '2 h. 3 min. 41 sec.';

Time totalTime = Time.newInstance(0, 0, 0, 0);
String[] arr = s.split('\\.');

for (String elem : arr) {
    elem = elem.trim();
    if (elem.contains('h')) {
        Integer hours = Integer.valueOf(elem.substring(0, elem.indexOf(' ')));
        totalTime = totalTime.addHours(hours);
    }
    if (elem.contains('min')) {
        Integer minutes = Integer.valueOf(elem.substring(0, elem.indexOf(' ')));
        totalTime = totalTime.addMinutes(minutes);
    }
    if (elem.contains('sec')) {
        Integer seconds = Integer.valueOf(elem.substring(0, elem.indexOf(' ')));
        totalTime = totalTime.addSeconds(seconds);
    }
}

System.debug(totalTime);

 
cldavecldave
Hi Bogdan, thank you for the info, this will be helpful in case I cannot do it via Formula and/or WF's   :)

I assume this method would need me to create triggers/test...

I was wondering in would be possible via Formula, Wf, process builder.... (no code)

Thx 
YuchenYuchen
You can try the following formula:

TEXT(IF(CONTAINS(Time1__c, "min"), VALUE(LEFT(Time1__c, Find("min", Time1__c)-2))*60 + VALUE(TRIM(MID(Time1__c, Find("min", Time1__c)+5, 2))), VALUE(SUBSTITUTE(Time1__c, " sec.","")))
+IF(CONTAINS(Time2__c, "min"), VALUE(LEFT(Time2__c, Find("min", Time2__c)-2))*60 + VALUE(TRIM(MID(Time2__c, Find("min", Time2__c)+5, 2))), VALUE(SUBSTITUTE(Time2__c, " sec.","")))
+IF(CONTAINS(Time3__c, "min"), VALUE(LEFT(Time3__c, Find("min", Time3__c)-2))*60 + VALUE(TRIM(MID(Time3__c, Find("min", Time3__c)+5, 2))), VALUE(SUBSTITUTE(Time3__c, " sec.","")))
+IF(CONTAINS(Time4__c, "min"), VALUE(LEFT(Time4__c, Find("min", Time4__c)-2))*60 + VALUE(TRIM(MID(Time4__c, Find("min", Time4__c)+5, 2))), VALUE(SUBSTITUTE(Time4__c, " sec.",""))))+" secs"

You may want to replace Time1__c, Time2__c, Time3__c, Time4__c with the API Name of your Custom Fields.

The formula will return the total number of seconds for the four fields.

Hopefully it helps. Thanks.
This was selected as the best answer
cldavecldave
Thank you very much Yuchen. It was exactly what I needed :)

You Rock!