Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Nesting IF Formulas with Check Boxes and Date fields

Topic Labels: Formulas
Solved
Jump to Solution
684 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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!