Conditional Date Check

[Howard_Goldstei]

I have a Project table with a Completed field which looks at a Final Date field and via a formula checks if today is past the Final Date field it marks the Completed field as YES for the Project record. I will be adding a trigger via Zapier to email someone that the Project record is complete. I need to add to the Completed field formula the ability to know if today is a weekend and if so to wait to mark the field as YES and trigger the email on the next weekday, Monday.

Thank you in advance.

[Zollie]

Your formula will likely involve the WEEKDAY function - it returns an integer standing in for the day of the week.

[Howard_Goldstei]

I worry that though the Final Date will have passed indicating a Completion, the WEEKDAY qualifier will keep looping and changing the status as time goes on due to it being a weekend day or not. How do I insure that the WEEKDAY function only affects the result once?

[Zollie]

I’ll get you started with some pseudocode (coding notes).

You already have this:

Completed field which looks at a Final Date field and via a formula checks if today is past the Final Date field it marks the Completed field as YES for the Project record.

Pseudocode:

IF(
	Today is after 'Final Date',
	"YES",
	"NO"
)

And now you’d like to add this functionality:

I need to add to the Completed field formula the ability to know if today is a weekend and if so to wait to mark the field as YES

Pseudocode:

IF(
	Today is a weekend,
	"NO",
	IF(
		Today is after 'Final Date',
		"YES",
		"NO"
	)
)

Here’s some additional documentation for IF statements.

[Howard_Goldstei]

There does not seem to be a formula value for WEEKEND. There is one for WEEKDAY, so I tried this:

IF({Final Shoot Date} = 0, “”, IF(WEEKDAY = TODAY(), “Monday”, IF(TODAY() > {Final Shoot Date}, “YES”, “”)))

I am trying first to leave the field blank if no value is entered in the Final Shoot Date field, then check if TODAY is a weekday, and if it is also past the Final Shoot Date value (if one is entered), mark Completed as YES, otherwise leave it blank. The above returns a formula formatting error. I also tried this:

IF({Final Shoot Date} = 0, “”, IF(TODAY() = WEEKDAY(), IF(TODAY() > {Final Shoot Date}, “YES”, “”))) and this did not return a formula formatting error, but did not work properly as records with a date before today (which is a weekday) did not register as YES.

Thanks again!

[Zollie]

Correct, you’ll be using WEEKDAY(). And yes, writing formulas means troubleshooting many error messages. Rather than trying to write the whole formula all at once, I’d recommend getting one chunk to work at a time, then combine them once each chunk is without errors.

Why you’re using WEEKDAY()

WEEKDAY() is used to convert dates into integers that represent the day of the week.

Today is Monday, so WEEKDAY(TODAY()) will return 1.

If it were Saturday, it would return 6, and if it were Sunday it will return 0.

So “Today is a weekend” could be written like this:

OR( WEEKDAY(TODAY()) = 0, WEEKDAY(TODAY()) = 6 )

[Howard_Goldstei]

Now I understand how WEEKDAY works, but am still having trouble getting this to format correctly:

IF({Final Shoot Date} = 0, OR( WEEKDAY(TODAY()) = 0, WEEKDAY(TODAY()) = 6 ), “”, IF(TODAY() > {Final Shoot Date}, “YES”, “”))

Tried just nested IFs instead of the OR and that still did not work. What am I missing?

[Zollie]

Howard_Goldstein:

{Final Shoot Date} = 0,

Not sure where this section came from or what it’s trying to say. Final Shoot Date is returning a date but you’re comparing it to an integer. You should have enough information now. Get each chunk working and read your error messages. Best of luck.

[Howard_Goldstei]

This table has historic information on projects where there was no data in the Final Shoot Date field, though we will be utilizing it going forward as described. I wanted to keep the filed blank if no data had been entered previously. This worked, as did processing the Final Shoot Date against TODAY. Where I have run into trouble is adding the capability to wait to mark the Completed field if TODAY is past the Final Shoot Date but it falls on a weekend day. The client does not want to send out an email on a weekend day (doing this via Zapier).

Thanks!

[Howard_Goldstei]

I think I have solved this in a 2 step process. In one formula field I check if the date field is blank, then I leave it as so and if not, if it is before today’s date. If the latter, I mark the field as true. In a second formula field I check that today is a weekday or not, and if it is a weekday mark it as true. Then in a third formula field I verify that both fields are true to show the project as completed, otherwise not. Tried many variations of trying to combine both tests in one formula and could not come up with it. My solution not as elegant but it seems to work. Thank you for all your assistance. It was much appreciated.