Skip to main content
Solved

Nesting IF Formulas with Check Boxes and Date fields

S

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

Best answer by JonathanBowen

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!
View original
Did this topic help you find an answer to your question?

2 replies

JonathanBowen
Forum|alt.badge.img+18
  • JonathanBowen
  • Inspiring
  • 1110 replies
  • Answer
  • June 25, 2020

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!
S
  • Shaun_Stanley
  • Author
  • New Participant
  • 1 reply
  • June 25, 2020
JonathanBowen wrote:

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!

Thank you so much for your quick help and feedback!

Reply


Most helpful members this week

Terms & Conditions