 ShowAll Questionssorted byDate Posted cldave

# 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 Best Answer chosen by cldave Yuchen
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. Shot
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(' ')));
}
if (elem.contains('min')) {
Integer minutes = Integer.valueOf(elem.substring(0, elem.indexOf(' ')));
}
if (elem.contains('sec')) {
Integer seconds = Integer.valueOf(elem.substring(0, elem.indexOf(' ')));
}
}

System.debug(totalTime);``` cldave
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 Yuchen
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 cldave
Thank you very much Yuchen. It was exactly what I needed :)

You Rock!