Relative date formula

[Andrew_Davies]

Hi all

I've been using Glide (www.glideapps.com) to create a front end, and their native DB has a lovely "Relative date" field. 

ie - post a comment, and that formula can be used to show "Posted just now" or "Posted 2 weeks ago". Before I write a huge Airtable formula so I can do it in my base - does anyone have any suggestions of the best way to do this?  ie 

If difference between now and {date} is :

• < 2 mins - return "just now"
• <59 mins - return "8 mins ago" (or however minutes
• >59 mins and <1440 - return "3 hours ago"
• >1440 and <10,080 - return "x days ago"

etc etc

Must be a better way than a huge IF statement?

Thanks

Andrew

[TheTimeSavingCo]

Hm, an IF seems like the way to go here.  You'd just need 6 IFs right?  "Just now", minutes, hours, days, months, years?  Each if would have its own "DATETIME_DIFF()" and you'd be good to go?

[Andrew_Davies]

Here is what I have ended up with :

IF(DATETIME_DIFF(NOW(), Date, 'y') > 0,

CONCATENATE("over ", DATETIME_DIFF(NOW(), Date, 'y'), " year", IF(DATETIME_DIFF(NOW(), Date, 'y') = 1, "", "s"), " ago"),

IF(DATETIME_DIFF(NOW(), Date, 'M') > 0,

CONCATENATE("over ", DATETIME_DIFF(NOW(), Date, 'M'), " month", IF(DATETIME_DIFF(NOW(), Date, 'M') = 1, "", "s"), " ago"),

IF(DATETIME_DIFF(NOW(), Date, 'w') > 0,

CONCATENATE("over ", DATETIME_DIFF(NOW(), Date, 'w'), " week", IF(DATETIME_DIFF(NOW(), Date, 'w') = 1, "", "s"), " ago"),

IF(DATETIME_DIFF(NOW(), Date, 'd') > 0,

CONCATENATE("over ", DATETIME_DIFF(NOW(), Date, 'd'), " day", IF(DATETIME_DIFF(NOW(), Date, 'd') = 1, "", "s"), " ago"),

IF(DATETIME_DIFF(NOW(), Date, 'h') > 0,

CONCATENATE("over ", DATETIME_DIFF(NOW(), Date, 'h'), " hour", IF(DATETIME_DIFF(NOW(), Date, 'h') = 1, "", "s"), " ago"),

IF(DATETIME_DIFF(NOW(), Date, 'm') > 0,

CONCATENATE("over ", DATETIME_DIFF(NOW(), Date, 'm'), " minute", IF(DATETIME_DIFF(NOW(), Date, 'm') = 1, "", "s"), " ago"),

"less than a minute ago"

)

)

)

)

)

)

I can not take the credit for this.  ChatGPT came to my aid.  I pasted it in and said "Can you change the datetime_diff statement so that if minute > 1 it shows minutes, and if minute = 1 it shows minute" and it did this in less than a second.

Unbelievable;  I need a sit down now