You need to sign in to do that

Don't have an account?

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

Thank you in advance :)

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 :)

YuchenYou 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

ShotSomething like that:

cldaveHi 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

YuchenYou 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.

cldaveThank you very much Yuchen. It was exactly what I needed :)

You Rock!