Match calculation on time/duration incrementing too quickly

I am trying to calculate (in mins) how long a ticket has been opened.

I normaly do (now - CreatedDate)2460 and it works. I am doing this in glide sheet, but every 5-10 seconds when it refreshed the time is incrementing by about 5mins.

Anyone seen this before ?

Don’t you want to do (now - CreatedDate)/60?

mmm interesting. This will work, but I think they have changed the algorithm/formula since I used it last.

Below is an old implementation where the one on the left is (now - create)2460 to get the mins since.
If I duplicate this now, it all breaks, giving me the issue I was seeing,
If I do the formula as suggested by you (now-create)/60 (third) it gives me hours:mins correctly and it matches my original formula

However, I am left with another challenge, in the original I had “mins” at the end, I have no idea I did that, is there a way to do this except for using an additional template column ?

There was a change made to the return value you get when subtracting one date from another in a Math column, not long after Glide added support for Airtable. About 2 years ago? Previously, (I think) it used to return an integer representing the number of days, but now it returns a duration in HH:MM:SS format. That’s the only change that I’m aware of.

You can add units to a Math column. If you edit the column, you should see how you did it.

I think it varies based on how the long the duration is. By default I think the duration is Hours:Minutes:Seconds so the multiplying or dividing by 60 is meant to offset the result to give the illusion that it’s something different. I think it’s more subjective in how your interpret the duration. Sometimes it looks like this 02:00 and sometimes it looks like this 00:02:00. What’s hours, what’s minutes, and what’s seconds n either situation? Kind of hard to tell.

I’ve seen formulas that multiple or divide by 60, but nothing that involves 24 since I don’t think the duration format is never formatted in days. Only hour, minutes and seconds.

image

Not getting the option to add the units. It is there until I put in the formula and it then it disappears. Seems to figure out that its a date and then refuses to let you add in units.

Since it’s a duration, it’s not a number. If you want units, you’d have to also create a template column to join the duration and units together.

Another option worth considering is the Relative Time column, although it’s a little more loosely based approximate time.

I wonder if you created that existing column before the change was made to return a duration instead of a number :thinking:

What do you see if you edit your original Duration column?

image
same thing

Well the original column created was before the change to the function and that is working fine. I tried to duplicate the formula on a new glide table and that is where I started to get this issue of 1. giving different answer to the calc (which you gave me the alternative to) and 2. not being able to add the units.

Here is the strange thing, on the original sheet I am seeing the units
image

but when I go to edit I am not seeing the unit configuration and the units vanish while in the edit mode of the column (see below)

Do you or anyone mind seeing what you get on your builder. This is being done in Glide sheets. Add a column → maths and add in the formla.

The column that has the createDate is one that is populated by the glide “current date/time” function.

In your second screenshot, the formula is returning a number. That’s why the units are available.

In your final screenshot, the formula is returning a duration, which is why there is no option to add units.

1 Like

ahh… ok found another post and connected the dots

round((now - creat)*24,2)*60

its works now.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.