Needing help with a formula

[Hannah_Morgan]

Hello! Noobie here trying to get a bit of help creating a formula. The goal is to get some visibility on when I last commented on a record vs when someone else last commented. This will help me see if they have replied or not so I know to follow up.

I already have two formula fields:

1. {last commented by me} (datetime) - this field is set with the timestamp of when I last commented
2. {last commented by someone else} (datetime) - this field is set with the timestamp of when anyone but me last commented

So far so good.

Now, let’s say I commented on a record 2 days ago, so the {last commented by me} field was set to 23/01/22 6:30 pm.

Let’s say it’s now 25/01/22 6:30 pm and I have not yet received a response, so the {last commented by someone else} field is empty, or maybe before 23/01. I’d like to be notified at this point so I can follow up.

My thinking is to have another formula that calculates the time from now to when I last commented - sort of like a timer.

DATETIME_DIFF(NOW(),{Last Commented by me},‘hours’)

If this “timer” field gets to 48, I should get an email (I can set that up via automation). But if someone comments and the {Last commented by someone else} field gets populated with a date after the 25th, the timer should almost reset to 0. It’s the latter part I’m struggling with…

Hope that all makes sense? Maybe there’s a simpler way to set this up?

Thanks in advance for any assistance!

[Elias_Gomez_Sai]

So you want to reply when you’re not the latest commenter, or followup when you’re waiting for 48+ hours, isn’t it?

You can use OR() and AND() function to join your 2 conditions. Also, I would make 2 different automations, one for each case with different email text.

[Hannah_Morgan]

Hi @Elias_Gomez_Sainz ! Thanks so much for your reply. Sorry, I’m new to formulas so I’m a bit lost. I sort of understand where you’re going, but not really sure how to implement it. Are you able to share an example of what the formula you’re proposing could look like? Appreciate your time! :slightly_smiling_face:

[Elias_Gomez_Sai]

I don’t have the time now, but I’ll try to explain later. Meanwhile you can check the help docs about it:

Airtable Support

Evaluate arguments using AND() and OR()

In this article, we'll primarily be working with two very similar formulas: AND(), and OR().  This article is part of a guided course which you can view here.   Click the cards below to flip them a...

[Hannah_Morgan]

Thanks @Elias_Gomez_Sainz ! Tried to figure it out by myself and I think I’m close? I’m trying to basically say:

If {last commented by me} is blank, OR if {last commented by someone else} is after {last commented by me}, then calculate the time difference from {last commented by me} to now.

Four example scenarios:

---

{last commented by me} = Jan 20
{last commented by someone else} = Jan 27

Expected result: formula field should show as “0” or NaN.

---

{last commented by me} = blank
{last commented by someone else} = Jan 27

Expected result: formula field should show as “0” or NaN.

---

{last commented by me} = Jan 26
{last commented by someone else} = blank

Expect result: formula field should show 24 hours (assuming it’s Jan 27th)

---

{last commented by me} = Jan 26
{last commented by someone else} = Jan 20

Expect result: formula field should show 24 hours (assuming it’s Jan 27th)

Here’s the formula I came up with:

(OR {last commented by someone else} = BLANK(), IS_AFTER {Last commented by someone else},{last commented by me}), DATETIME_DIFF(NOW(),{last commented by me},'hours'))

However, I’ve formulated it wrong as it’s throwing an error when I try and save it.

Again, appreciate your help :)))