As notes by @Paul_Danyliuk in another of my posts, small but really annoying errors can creep into your docs due to the way coda handles time/durations.
Since I’ve recently built a module for our project management system to send data to be invoiced, I needed to do something about it. We kept getting things like 1.998 hours in invoices rather than 2 hours etc.
Indeed - here’s my test table (with fake data) and you can see two errors there…
Anyway. So with some bfbi (brute force and bloody ignorance) and just enough regex knowledge to hang myself, I came up with the following formula…
(tonumber(RegexReplace(totext(thisRow.[Duraction),"hr?.*","")))+ (tonumber(substitute(RegexReplace(totext(thisRow.[Duration]),"(.*)(?<=(hrs?))","")," mins",""))/60)
It appears to work. Whats going on? The first part just matches “hr” and everything after it, and gets rid of it, turning the result to a number. That gets the hours part.
For mins, its a tiny bit trickier yet similar. I use regex to get rid of everything up to and including hr or hrs, and then substitute to get rid of the mins. Divide the result by 60 to get the fraction of the hour, and add it to hours. Meaning
2 hours 45 mins becomes 2.75 - and is ALWAYS 2.75 (and doesn’t have the errors when trying to do this via other methods)
Is there an easier way? Can anyone see any problems with my hacky solution?