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.
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.
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.
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
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)