Help

Re: Nesting IF Formulas with Check Boxes and Date fields

Solved
Jump to Solution
505 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Shaun_Stanley
4 - Data Explorer
4 - Data Explorer

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”)

1 Solution

Accepted Solutions
JonathanBowen
13 - Mars
13 - Mars

Hi @Shaun_Stanley - you can do something like this:

Screenshot 2020-06-25 at 18.10.35

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!

See Solution in Thread

2 Replies 2
JonathanBowen
13 - Mars
13 - Mars

Hi @Shaun_Stanley - you can do something like this:

Screenshot 2020-06-25 at 18.10.35

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!

Thank you so much for your quick help and feedback!