Skip to main content

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

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!

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


Terms & Conditions