Elapsed time from a system column to the current datetime

Xochitl C.

Hi guys, I need some help to calculate the hours and minutes have elapsed from a system column (which is the ticket creation date) to the present date, not sure if it is possible to use TODAY() formula.

Here are some screenshots of what I have so far, but seems like the minutes calculation is not working.

The ultimate goal is to create an alert for unsolved tickets after 24 hours, that's why I would like to calculate HH:MM. If you have another suggestion, I would appreciate any insight.

Paul Newcome

Give this a try:

=IF(VALUE(YEAR(Modified@row) + "" + RIGHT("0" + MONTH(Modified@row), 2) + "" + RIGHT("0" + DAY(Modified@row), 2) + RIGHT("0" + (VALUE(MID(Modified@row, FIND(" ", Modified@row) + 1, FIND(":", Modified@row) - (FIND(" ", Modified@row) + 1))) + IF(MID(Modified@row, FIND(" ", Modified@row) + 1, FIND(":", Modified@row) - (FIND(" ", Modified@row) + 1)) <> "12", IF(FIND("PM", Modified@row) > 0, 12, 0), IF(FIND("PM", Modified@row) > 0, 0, -12)), 2) + "" + MID(Modified@row, FIND(":", Modified@row) + 1, 2)) - VALUE(YEAR(Created@row) + "" + RIGHT("0" + MONTH(Created@row), 2) + "" + RIGHT("0" + DAY(Created@row), 2) + RIGHT("0" + (VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1)) <> "12", IF(FIND("PM", Created@row) > 0, 12, 0), IF(FIND("PM", Created@row) > 0, 0, -12)), 2) + "" + MID(Created@row, FIND(":", Created@row) + 1, 2)) > 10000, 1)

You will want to make sure your column names are used in the formula. This is only comparing the Modified to Created. To get Modified updated on a regular basis, the best we can do is hourly and when something in the row is otherwise changed. For this you would set up 24 separate automations. All set to run daily, but each set to run at a different hour. I have them all set as Update Cell automations feeding a text/number column and outputting a different value for each hour.