"Time Ago..." relative time in the style of social media

[TyAlevizos]

This is either a tips and tricks post or it’s a call to make this better. Could go either way!

Here’s an app that demonstrates “time ago”. Is there a better way to do this?

https://www.appsheet.com/samples/Demonstrates-the-classic-Time-Ago-calc-a-la-social-media?appGuidStr...

First we need a calc named “Time ago in Seconds” which is a unix timestamp derived from a Datetime field called “Timestamp”:

((HOUR(DATE(NOW()) - “01/01/1970”))*3600 + (MOD(HOUR(NOW() - “01/01/1970”),HOUR(DATE(NOW()) - “01/01/1970”)))*3600 + MINUTE(NOW() - “01/01/1970”)*60 + SECOND(NOW() - “01/01/1970”)) -
((HOUR(DATE([Timestamp]) - “01/01/1970”))*3600 + (MOD(HOUR([Timestamp] - “01/01/1970”),HOUR(DATE([Timestamp]) - “01/01/1970”)))*3600 + MINUTE([Timestamp] - “01/01/1970”)*60 + SECOND([Timestamp] - “01/01/1970”))

Then we need a IFS() calculation that looks like so:

ifs(
[Time ago in Seconds] < 0, "hasn't happened yet, why worry?",
[Time ago in Seconds] < 5, "just now",
[Time ago in Seconds] < 15, "a few seconds ago",
[Time ago in Seconds] < 60, [Time ago in Seconds] & "s ago",
[Time ago in Seconds] < 3600, [Time ago in Seconds]/60 & "m ago",
[Time ago in Seconds] < 86400, ([Time ago in Seconds]/60)/60 & "h ago",
[Time ago in Seconds] < 604800, (([Time ago in Seconds]/60)/60)/24 & "d ago",
[Time ago in Seconds] < 2419200, ((([Time ago in Seconds]/60)/60)/24)/4 & "w ago",
[Time ago in Seconds] < 29030400, ((((([Time ago in Seconds]/60)/60)/24)/4)/12)+1 & "mo ago",
[Time ago in Seconds] >= 29030400, ((((((([Time ago in Seconds]/60)/60)/24)/4)/12)+1)/12)+1 & "y ago"
)

Open to ideas here… thanks for reading.

[MultiTech]

TyAlevizos:

First we need a calc named “Time ago in Seconds” which is a unix timestamp derived from a Datetime field called “Timestamp”:

((HOUR(DATE(NOW()) - “01/01/1970”))*3600 + (MOD(HOUR(NOW() - “01/01/1970”),HOUR(DATE(NOW()) - “01/01/1970”)))*3600 + MINUTE(NOW() - “01/01/1970”)*60 + SECOND(NOW() - “01/01/1970”)) -
((HOUR(DATE([Timestamp]) - “01/01/1970”))*3600 + (MOD(HOUR([Timestamp] - “01/01/1970”),HOUR(DATE([Timestamp]) - “01/01/1970”)))*3600 + MINUTE([Timestamp] - “01/01/1970”)*60 + SECOND([Timestamp] - “01/01/1970”))

TOTALSECONDS(NOW() - [Timestamp])

While not a Unix timestamp calc, it should produce the same result.

help.appsheet.com

TOTALSECONDS()

Count of seconds in Duration

[TyAlevizos]

that’s way cleaner, thank you!!

[MultiTech]

There’s so many of these time-saving formulas that have been snuck into the system throughout the years.

[Suvrutt_Gurjar]

A very useful concept as usual from you @TyAlevizos. Thank you.

If I may say, in essence, you have nicely converted the cryptic computer science language on the left-hand side of the expression into an art of human-friendly readable language on the right hand side.