Populate a Past Due Notice using EndDate and Computer's Clock

[Scott_Burton]

Hello Everyone and forgive if this question has been answered in a previous Post.

Quick background. I created a StartDate using the Date field. Then I created a second column to enter the number of days that the project is expected to be completed. This field is a ‘Number’ field called Duration. Then I created a third field called EndDate which is a formula field where it calculates the number of days assigned to the project with the Start Date to acquire the End Date value. The formula is

DATEADD(StartDate, Number, ‘day’)

First question, is there a way to exclude ‘weekends’ so number of actual days would be business days?

Second challenge - Using the EndDate information, if the project goes past the EndDate by more than 2 business days, I would like that to be populated in a PastDue field where it would state: ‘Past Due by 2 Days’. I would think this has to be tied into using the Computer Clock where it compares the value in the EndDate to the Computer Clock Date.

This is my main question, how can i create a formula field that identifies when a project exceeds the End Date? I would assume it has to be tied to the Computer’s Clock to determine that???

[Mohamed_Swella1]

HI @Scott_Burton,

Welcome to Airtable Community!

Scott_Burton:

First question , is there a way to exclude ‘weekends’ so number of actual days would be business days?

Yes, you can use this formula

WORKDAY_DIFF(startDate, endDate, [holidays])

Scott_Burton:

Second challenge - Using the EndDate information, if the project goes past the EndDate by more than 2 business days, I would like that to be populated in a PastDue field where it would state: ‘Past Due by 2 Days’. I would think this has to be tied into using the Computer Clock where it compares the value in the EndDate to the Computer Clock Date.

You can use a formula such as IF(DATETIME_DIFF({EndDate, Today(), ‘days’)>2, " Past Due ", “”)

I believe this also answers your last question?

BR,
Mo

[Scott_Burton]

Regarding the first question, i am thinking i will just use AirTable’s Calendar field type for both Start Date and End Date, allowing user to select the start and end dates then based on those two fields, it will calculate the total number of days. But how can I get it to only show Business days as opposed to including Calendar days which include Weekends?

As for my second question, I think i was successful in addressing this question. This is what i did, using the date from EndDate field (calendar type), i created a formula field called PastDueDays which tells me how many days the project has exceeded from Today’s date:

Here is that formula, i used in the PastDueDays field:
DATETIME_DIFF(EndDate, TODAY(), ‘days’)

So when the EndDate was 3/1/2020 and ‘today’s date is 3/12/2020’ - it generated a number value of 12.

Next I created a PastDueStatus formula field where- if today’s date is greater than EndDate, the word “Past Due” would appear. The formula was:

IF(PastDueDays > 1, “Past Due”)

Was successful!..

…But what i like to do is have several IF scenarios like this:
IF(PastDueDays > 1 or< 5, “Past Due”), IF(PastDueDays >= 5, “Outstanding”)
Now that statement does not work. So if anyone can help me understand how to create a multi-condition IF statement (yes, i am not a programmer). I would appreciate

Also, if I wanted to color code this new PastDueStatus based on the output value of Past Due or Outstanding, is that possible?

[Scott_Burton]

Thank you so much as i kept digging into the AirTable Community and found this as a great way for a Project Manager to identify those projects on time or past due or outstanding which included Checklist visuals for each type:

IF(
{DueDateDays} < 0,
“ :white_check_mark: On Time”,
IF({DueDateDays} > 80,
“ :x: Outstanding”,
“ :muscle: Meets expectations”
)
)

[Scott_Burton]

My mistake, why is it I always think in reverse. The point is that if the End Date is older than Today’s Date, the Due Date Days count would be a negative number, meaning past due. Positive Days means that End Date is after Today’s Date, hence positive number…So i reversed engineered it and this formula is what I am seeking.

IF(
{DueDateDays} < 0,
“ :x: Past End Date”,
IF({DueDateDays} = 0,
“ :muscle: On Time”,
“ :white_check_mark: Not Past End Date”
)
)

[Scott_Burton]

Thank you for your prompt response :grinning: