Nesting IF Formulas with Check Boxes and Date fields

[Shaun_Stanley]

Hello! I am using airtable to track tasks that have been completed before a new hire starts. I currently have a Start Date (Field type is Date) and a Laptop Ordered (Field type is Checkbox). I would like to create an IF formula that comes down to: if laptop order is not checked AND the start date is less than 7 days, i get a message that says “late: needs ordering”

I cannot figure out how to do IF functions with dates and then how to incorporate it into a larger, nested formula.

Currently all I can get is the Check box to work

I have: IF({Laptop Ordered}=1,“On Track”,“Late: Needs Ordering”)

[JonathanBowen · Accepted solution]

Hi @Shaun_Stanley - you can do something like this:

The formula field is:

IF(
  AND(DATETIME_DIFF({Start Date}, TODAY(), 'days') < 7, NOT({Laptop Ordered})), 
  'Late', 
  'On track'
)

So here we’re saying:

if the start date is less than 7 days AND the laptop is not ordered, then show as ‘late’, otherwise ‘on track’

Aside: if you have many tasks to complete when a new hire starts, then you might not want to use this base structure, i.e. a field/column and associated formula field for every task, as this isn’t particularly scalable.

You might want to consider a 3 table solution:

• People/hires - names, details start dates
• task to be completed - description and lead time days
• a join table that links the two, so a row for each person/task combination

With this sort of setup your tasks become rows rather than columns, which is easier to manage for multiple tasks.

---

If this answers your question, please consider marking it as the "solution". If not, please post again for more help. Thanks!

[Shaun_Stanley]

Thank you so much for your quick help and feedback!