Help

Re: Complex task overdue

1344 0
cancel
Showing results for 
Search instead for 
Did you mean: 
ana_velingard
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi everyone
I have a habit/task table, the time period is on a multiple choice column (daily, weekly, bi-weekly monthly, etc.). I keep track of the last time it’s done (the date).
Is there a formula that can take both into consideration and tell me if something is overdue?
for example if it has been more then a week since i did a weekly task.

13 Replies 13
Julian_E_Post
8 - Airtable Astronomer
8 - Airtable Astronomer

A formula field with a switch statement should work well for this. I created an example base with this formula that calculates a “due date” for each case:

SWITCH( 
		Frequency, 

			'Daily',DATEADD({Last Completed},1,'day'),
			'Weekly',DATEADD({Last Completed},1,'week'),
			'Biweekly',DATEADD({Last Completed},2,'week'),
			'Annually',DATEADD({Last Completed},1,'year')
)

“Frequency” is the field where you will select the time period of the task. Each case adds the correct time period. Then you can create a second field that compares the “due date” against the current day:

IF(TODAY()>{Due Date},"Yes","No")

Tasks Example Base

Screen Shot 2020-10-03 at 11.15.51 PM

The due date thing doesn’t work

Try clicking on the example base that I linked to and look at how it’s set up. Does the column naming in your base match the column naming in the example?

It doesn’t, i changed the names accordingly but it doesn’t work, it says something is wrong but i don’t see what

Julian_E_Post
8 - Airtable Astronomer
8 - Airtable Astronomer

Can you upload a screenshot of the code?

photo6048761958498415751

It looks like the brackets with punctuation in front of each case may be your issue. What is the purpose of that? If you paste the code here, I’ll make a couple edits for you

SWITCH({time frame (from full contact list)},{.}‘daily’,DATEADD({Last Catch-up},1,‘day’),{.}‘twice a week’,DATEADD({Last Catch-up},3,‘day’),{.}‘weekly’,DATEADD({Last Catch-up},1,‘week’),{.}‘bi-weekly’,DATEADD({Last Catch-up},2,‘week’),{.}‘monthly’,DATEADD({Last Catch-up},1,‘month’),{.}‘6 months’,DATEADD({Last Catch-up},6,‘month’))

they don’t have a purpose it’s just what the computer gave me when i tried changing things.
thank you for your help.

Try this:

SWITCH(

	{time frame (from full contact list)},

		'daily', DATEADD({Last Catch-up},1,'day'),

		'twice a week',DATEADD({Last Catch-up},3,'day'),

		'weekly',DATEADD({Last Catch-up},1,'week'),

		'bi-weekly',DATEADD({Last Catch-up},2,'week'),

		'monthly',DATEADD({Last Catch-up},1,'month'),

		'6 months',DATEADD({Last Catch-up},6,'month')
)

i am sorry to bother you again, i have to lists and it doesn’t work in my other one, i don’t know what’s my mistake
this is the formula:
SWITCH(

{Frequency},

‘multiple times a day’, DATEADD({Last time done},1,‘day’),

daily’, DATEADD({Last time done},1,‘day’),

‘twice a week’,DATEADD({Last time done},3,‘day’),

‘weekly’,DATEADD({Last time done},1,‘week’),

‘bi-weekly’,DATEADD({Last time done},2,‘week’),

‘monthly’,DATEADD({Last time done},1,‘month’),

‘3 months’,DATEADD({Last time done},3,‘month’),

‘as needed’,DATEADD({Last time done},3,‘month’),

‘6 months’,DATEADD({Last time done},6,‘month’),

‘yearly’,DATEADD({Last time done},1,‘year’)

)

also is there a way to make it ignore empty cells rather then say they are overdue?

Julian_E_Post
8 - Airtable Astronomer
8 - Airtable Astronomer

Airtable is weird about quotation marks. Sometimes if you copy and paste from a different program, you end up with the directional quotations like in the above text, which it doesn’t like. Try redoing those - otherwise, your code looks great! In terms of empty cells, you could do something like this:

IF({Last Completed},IF(TODAY()>{Due Date},"Yes","No"),"")

The first IF() checks to see if there’s anything in “Last completed”, and if yes, it goes to the second IF(). You could also make a case in the Switch() statement to cover this so that it doesn’t show #ERROR when the cell is empty.

I didn’t do it in a different program i did it on airtable, that’s what’s weird.

It’s my first time trying, never tried to code before and was just trying to make it like the previous one you sent.
Don’t know how to code so i didn’t understand the last part about using a switch to make it ignore empty cells, currently I’m using only the yes rather then yes and no to make it pop out.

Julian_E_Post
8 - Airtable Astronomer
8 - Airtable Astronomer

I looked over your code again. Look at the third line. ‘Daily’ is missing the first quote