Best Of

Here is a way to create reminders for Proof reviewers. This solution will:

• Send a reminder 5 days after the initial invite, if no one has responded to a Proof review request.
• Send a reminder 5 days after the last reminder, if no one has responded to a Proof review.
• Reset when a new Proof version is created.

Some useful modifications include:

• Reminding any one who has not responded to a Proof review request.
• Changing the reminder frequency for urgent Proofs or conditions.

To create this solution, follow these steps:

1. Create the Proof Info columns. These will reveal information such as who has responded, whose response is pending, and status of the proof.

1. Create these columns

• Invite sent date.
  • Date column.
    • Will be filled with automation when invites are sent.
• Workdays since invite sent.
  • Formula
  • =IF([Invite sent date]@row = "", "", NETWORKDAY([Invite sent date]@row, TODAY()) - 1)
• Send proof review reminder.
  • Trigger to send reminder notification. In this case the reminded is sent after 5 days if no one has responded.
  • Formula
  • =IF(AND([Workdays since invite sent]@row >= 5, [Contract draft status]@row = "In Review", OR([Workdays since reminder sent]@row = "", [Workdays since reminder sent]@row >= 5)), "Yes", "No")
• Last reminder sent.
  • Date column.
  • Will be filled with automation when a reminder is sent.
• Workdays since reminder sent.
  • Formula
  • =IF([Last reminder sent]@row = "", "", NETWORKDAY([Last reminder sent]@row, TODAY()) - 1)

1. Create two automations

I hope this helps.

Neil Egsgard

Business Solutions Architect

Southern Alberta Institute of Technology

This little formula is useful for a range of solutions, like working out if a date is in daylight savings time or not. The examples below reference a date field named [Date].

The first part of the formula finds the date of the seventh day of the month. This is done so that the number of days from Sunday can be subtracted:

= DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 )

The second part of the formula finds the number of days to subtract from that date:

- ( WEEKDAY( DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) ) - 1 )

This part of the formula gets the date of the seventh day of the month again, and works out what day of the week it is. We subtract 1 from the result because the WEEKDAY() function returns values starting at Sunday = 1. So if the seventh day of the week just happens to be Sunday we don't want to subtract 1, we need to subtract 0 instead.

So the entire formula to find the date of the first Sunday of a month is:

= DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) - ( WEEKDAY( DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) ) - 1 )

This can be tweaked a little for applications like checking if a date is/was in daylight savings time. This example expects daylight savings time to start on the first Sunday in October and end on the first Sunday in April of the next year:

=IF( AND( [Date]@row >= DATE( YEAR( [Date]@row ), 10, 7 ) - ( WEEKDAY( DATE( YEAR( [Date]@row ), 10, 7 ) ) - 1 ), [Date]@row < DATE( YEAR( [Date]@row ) + 1, 4, 7 ) - ( WEEKDAY( DATE( YEAR( [Date]@row) + 1, 4, 7) ) - 1) ), 1, 0)

It checks to see if [Date] is greater than or equal to the first Sunday in October (month 10) in the same year as [Date] AND is less than the fist Sunday of April (month 4) in the following year. If that is true then it returns 1 (daylight savings), otherwise 0 (not daylight savings).

To find the date of the first day of the month for some other weekday a slight tweak is required on the second part of the formula:

= DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) - MOD( ( WEEKDAY( DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) ) - 1 ) + [Weekday], 7)

where [Weekday] contains:

• 0 for Sunday
• 1 for Monday
• 2 for Tuesday
• 3 for Wednesday
• 4 for Thursday
• 5 for Friday
• 6 for Saturday