Total time with HH:MM:SS in a Text field

Platform discussions Special workflows & use cases
1

Hello,

I have 2 text columns. Each column has a time in it with the format of HH:MM:SS. I cannot put this in to an hour field for other reasons.

I am trying to get the time spent or difference of time between the 2 columns via a formula but have not had good results with the formulas I’ve used.

Any help is appreciated,

Brad

2

Hey @bbihun,

Would you be happy to share your formula with us? Currently the hours_difference formula only works for hour columns, which might explain why you’re experiencing this issue with the text columns.

3

@bbihun

This formula will return the difference in hours for two text columns in the format you specified:

((REGEXEXTRACT("0" & {Text2}, "(\d*):") * 3600 +
  REGEXEXTRACT({Text2}, ".*:(\d*):") * 60 +
  REGEXEXTRACT({Text2}, ".*:.*:(\d*)")) -
(REGEXEXTRACT("0" & {Text1}, "(\d*):") * 3600 +
  REGEXEXTRACT({Text1}, ".*:(\d*):") * 60 +
  REGEXEXTRACT({Text1}, ".*:.*:(\d*)")))
/ 3600

Jim - The Monday Man
:magic_wand: Update Magic #1 – New update enhancement toolbox
:magic_wand: Column Magic :sparkles:– The magical columns toolbox
We Create Custom Solutions

4

Hello @JCorrell,

Thank you very much for this. I put in the formula and get the results as a decimal. For example the result I get for this one is 0.058.

The End Time is: 11:45:02
The StartTime is: 11:41:33

Is there a way to get the results in MM:SS. Looking to get the total time duration between those 2 times.

Thanks again

5

@bbihun

If the first formula is named “Duration”, this is one way:

TEXT(INT(MOD({Duration}, 1) * 60), "00") & ":" &
   TEXT(INT(MOD({Duration} * 60, 1) * 60), "00")

Jim - The Monday Man
:magic_wand: Update Magic #1 – New update enhancement toolbox
:magic_wand: Column Magic :sparkles:– The magical columns toolbox
We Create Custom Solutions

1 Like
6

Hello Jim,

Thank you for the continued support. That is not working. Here is a visual of the columns that may help.

image
image1236×152 14.4 KB

7

@bbihun

Those values are working for me. Please show the actual formula text for both formulas as they exist on your board. Also, what is the message shown when you hover over the red exclamation mark?

8

Hi Jim, Looks like it works if I add another formula to the right of the output formula. Is there a way to only have 1 formula column and have it output as shown in Total Video Time 2

image
image1618×140 21.3 KB

9

@bbihun

Sure. Just combine them:

TEXT(INT(MOD(
   (((REGEXEXTRACT("0" & {Text2}, "(\d*):") * 3600 +
     REGEXEXTRACT({Text2}, ".*:(\d*):") * 60 +
     REGEXEXTRACT({Text2}, ".*:.*:(\d*)")) -
   (REGEXEXTRACT("0" & {Text1}, "(\d*):") * 3600 +
     REGEXEXTRACT({Text1}, ".*:(\d*):") * 60 +
     REGEXEXTRACT({Text1}, ".*:.*:(\d*)")))
   / 3600)
, 1) * 60), "00") & ":" &
TEXT(INT(MOD(
   (((REGEXEXTRACT("0" & {Text2}, "(\d*):") * 3600 +
     REGEXEXTRACT({Text2}, ".*:(\d*):") * 60 +
     REGEXEXTRACT({Text2}, ".*:.*:(\d*)")) -
   (REGEXEXTRACT("0" & {Text1}, "(\d*):") * 3600 +
     REGEXEXTRACT({Text1}, ".*:(\d*):") * 60 +
     REGEXEXTRACT({Text1}, ".*:.*:(\d*)")))
   / 3600) *
60, 1) * 60), "00")

Jim - The Monday Man
:magic_wand: Update Magic #1 – New update enhancement toolbox
:magic_wand: Column Magic :sparkles:– The magical columns toolbox
We Create Custom Solutions

2 Likes