# Relative date formula

Topic Labels: Formulas
1031 2
cancel
Showing results for
Did you mean:
7 - App Architect

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

2 Replies 2
18 - Pluto

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?

7 - App Architect

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