How to create the formula for a workflow and automation

Patrick Tan

How to create the formula for a workflow and automation to monitor and execute a task on a birthday listing on an annual basis? Million Thanks in Advance.

Andrée Starå

Hi @Patrick Tan

I hope you're well and safe!

Try something like this.

Option 1

Add a recurring annual workflow to trigger from the date.

=IFERROR(DATE(YEAR(TODAY()), MONTH([Date of birth]@row), DAY([Date of birth]@row)), "")

Option 2

This will check if the birthday is in the past and update to show next year's birthday.

=IFERROR(IF(DATE(YEAR(TODAY()), MONTH([Date of birth]@row), 
DAY([Date of birth]@row)) < TODAY(), DATE(YEAR(TODAY()) + 1, 
MONTH([Date of birth]@row), DAY([Date of birth]@row))), "")

Would one of those options work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Patrick Tan

I hope you're well and safe!

Try something like this.

Option 1

Add a recurring annual workflow to trigger from the date.

=IFERROR(DATE(YEAR(TODAY()), MONTH([Date of birth]@row), DAY([Date of birth]@row)), "")

Option 2

This will check if the birthday is in the past and update to show next year's birthday.

=IFERROR(IF(DATE(YEAR(TODAY()), MONTH([Date of birth]@row), 
DAY([Date of birth]@row)) < TODAY(), DATE(YEAR(TODAY()) + 1, 
MONTH([Date of birth]@row), DAY([Date of birth]@row))), "")

Would one of those options work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Patrick Tan

Hi @Andrée Starå

A million thanks to you. you are always here to help :)))

Let me try it and keep you posted.

Patrick

Patrick Tan

Hi @Andrée Starå

Both options work well. Great.

By any chance, do you have a solution for Q4? to highlight the row in "Yellow" if the month to follow up falls on the current.

For example, this month is Oct, therefore all rows that fall within Oct under the "next follow-up date" column will be highlighted in "Yellow"

Many thanks in advance.

Patrick

Andrée Starå

@Patrick Tan

Excellent!

You're more than welcome!

For the highlight option

Try something like this.

1. Add a so-called helper checkbox column (Current Month)
2. Add the following formula and then reference the checked box in a Conditional Formatting rule

=IF(MONTH(TODAY()) = MONTH([Next follow up date]@row), 1)

Make sense?

Did that work?

Patrick Tan

Hi @Andrée Starå

Million Thanks for your help. All are up and running.

Patrick

Andrée Starå

@Patrick Tan

Excellent!

You're more than welcome!