Correcting Time Duration Errors using Regex (hacky!)

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?

cheers, Brendan.

There is definitely an easier way, lol. But I’m not at the computer right now.

The idea is this: instead of regex and toText and toNumber and stuff here and back, you can extract .Minute() and .Hour() out of duration, round to integers, and then calculate the rate and render to text out of those.

1 Like

Oh - I forgot to add I was deliberately trying to do it WITHOUT round. It just feels wrong to use round in any calculation which has to do with financials. In the general scheme of things, I’m sure it would never matter. The biggest error I have seen is perhaps around 1%.

Doesn’t it feel wrong in general to use floating point math for financial calculations then?

Just count the integer number of minutes and use that in all calculations. You can render that as duration at any time.

BTW financials are still rounded to cents at least, no?

1 Like

You are of course correct…I just set myself a silly mission.

Didn’t mean to call it silly :slight_smile: I’m a perfectionist myself. I would’ve just used integer minutes (maybe as an intermediary calculation).

1 Like

So I ended up using Round() in some of my formulas to help with this… and today found a situation where it wasn’t accurate enough.

So here I was trying to get the number of minutes, and round them due to the errors. And we see it is out by a full minute, which down the line caused inaccurate amounts in an invoice.

So I’m now going to round to the nearest 15 mins using roundto () - since we don’t deal with smaller units than 15 mins… and hope the error never gets that far. Time will tell. Maybe my regex hack wasnt so silly :slight_smile:

Don’t use .ToNumber() * something * something.

Use [Billable Hours].ToMinutes()

@BenLee can you please look into the .ToNumber() rounding bug? I came across it myself just tonight. It doesn’t accurately convert durations to days.

I hadn’t noticed this before. Thanks for both for finding and verifying it.

I’ve got this table setup to show things, if you’re seeing more or something else, let me know so I can add it…

image