Help with a function or maybe it's a formula

[Sonja_Seglin]

Hi, I hope I’m posting in the right place. Brand new to Airtable. My goal is to set up a CRM for networking that will remind me when to reach out. My plan:

1. Each person (record) gets a numeric rating to indicate how often I want to be in touch with them.
2. I will have a field where I manually enter the date of most recent contact.
3. I want another field that will calculate the next date needed, based on the rating. So, if the rating field has a 1, the field with the next contact needed will be 30 days, if the rating is 2, 60 days, etc.
4. I’d like conditional formatting to turn the record a different color if I’m overdue.

I am really not well versed with formulas or functions so I have no idea how to set this up.
Can anyone help me? Thank you!

[kuovonne]

Welcome to the Airtable community!

You want a formula field. You will need the DATEADD() function.

You can convert the rating into the number of days to by either multiplying the rating times 30, or you can use a SWITCH() function.

To check if the record is over due, use another formula field that compares the due date with TODAY().

These functions are documented in the Formula Field Reference.

For record coloring, see this support article.

[Databaser]

Hi @Sonja_Seglin

Something like this?

"Next date"

SWITCH(Rating,1,DATEADD({Most recent contact},30,"days"),2,DATEADD({Most recent contact},60,"days"),3,DATEADD({Most recent contact},90,"days"))

"Overdue?"

IF({Next date}<TODAY(),"yes","no")