Skip to main content

Hello everyone!

Right now I need to create a CRM system for an organization I work with. The point is that each project has several statuses (e.g. order received, pending order, editing etc.). The part I stucked at is that each part has two dates linked to it - planned date of delivery and actual date of delivery (to control the timing of each stage). Could you please advise how to provide the most optimal solution in Airtable, thanks!

Hi and welcome @Semyon_Sushich!
I would add a formula field that monitors the status of the project and outputs

  1. if the project have been delivered.
  2. if today is before planned day of delivery and if so, how many days that are left.
  3. If today is after planned day of delivery and no delivery has been made and if so, how many days it’s overdue.

I like to add emojis to my status formulas, but they can easily be removed if you prefer not to use them.

You need 3 fields to achieve this:

  1. {planned date of delivery} = Date field
  2. {actual date of delivery} = Date field
  3. {formula field} = Formula field

In the formula field you add the following formula

IF({actual date of delivery},
	'✅ Delivered',
	IF({planned date of delivery}=BLANK(),
		'',
		IF(
			IS_BEFORE(
				TODAY(),
				{planned date of delivery}
			),
			'⏳ '&DATETIME_DIFF({planned date of delivery},TODAY(), 'days')&' day(-s) left to planned date of delivery',
			'🔴 '&DATETIME_DIFF(TODAY(),{planned date of delivery}, 'days')&' day(-s) overdue'
		)
	)
)
Terms & ConditionsAccessibility statement