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
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.
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
Update Magic #1 – New update enhancement toolbox
Column Magic 
– The magical columns toolbox
We Create Custom Solutions
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
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
Update Magic #1 – New update enhancement toolbox
Column Magic – The magical columns toolbox
We Create Custom Solutions
Hello Jim,
Thank you for the continued support. That is not working. Here is a visual of the columns that may help.
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?
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
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
Update Magic #1 – New update enhancement toolbox
Column Magic – The magical columns toolbox
We Create Custom Solutions
Hi @JCorrell
Found this old post. I have a similar issue, but I need to output HH:MM:SS.
My formula skills is limited, so I need some help. Is it possible to modify the formula to do this?
