Format date equation to get birthdate in current year

I’m trying to convert a Date of Birth field to another field so that I can get the birthday in the current year that’s a few less steps than this workflow Show Upcoming Birthdays - Knack Knowledge Base.

I have getDateMonth({DOB})/getDateDayOfMonth({DOB})/getDateYear((currentTime()) entered, but not sure how to escape or enter the dividing “/” between each of the components to be read by the equation.

Anyone else have any luck with setting up a formula like this?

Hi

As far as I know, I don’t think you can do this in Knack equations - in your example the / characters are presumably being interpreted as divisions?

When confronted with stuff like this, I often resort to Integromat to do the calculations. Let start with looking at the fields you need:

There are the two date fields and then a field to show if the Birthday this year field is actually in the current year - this is a Yes/No field with conditional rules:

Now, in Integromat, create a Scenario which has a Search For Records Knack module set up like this:

Set this to run periodically as your volume etc demands.

The second module is then a simple Update a Record module which sets the birthday this year:

Running this will update the records above to:

The combination of Knack and Integromat gives you a toolset that can do almost anything - I can’t really see using one without the other these days!

Hope this helps!

Julian

@JulianKirkness thanks for the quick reply! I was really hoping there was a way to escape characters in the equation. I’m trying really hard to not use any other integrations to keep the toolkit complexity low for the organization I’m working for.

Thanks again!

1 Like

Adding a simpler solution to this thread that I was able to put together. Hope it’s helpful for someone else too!

This is done with two additional fields:

  • “Birthday MM/DD (TF)”. Text formula field that uses the Date Formatter function to get the month and day of the birthday from the DOB field - formatDate({DOB}, MM/DD)
  • “Birthday This Year”. Date field with a Default Date = Current Date, and a Conditional Rule to set the value to the Text Formula field “Birthday MM/DD (TF)”.

Then, a scheduled task on the Patient object (object_3/task_23) sends an email for upcoming birthdays with the following criteria:

  • Schedule = Weekly
  • Sends on Mondays at 6am
  • Birthday This Year = after the next 6 days
  • Birthday This Year = during the next 13 days

Hi Laura

That is very clever!

However, I have found two issues with it which may or not matter in your case…

  • This technique doesn’t seem to work correctly for people born on the 29th February - the simple solution would be simply to enter these as the 28th I guess
  • This will calculate their birthday in the current year (more specifically the year in which their record was created. You can get round this (I think) by adding a date equation set to currentTime() and then build your text formula to include that:

Screenshot 2021-09-12 at 15.47.33

(Note I am in the UK so DD/MM/YYYY)

You then use the same Date field with conditional rule to populate it from the text formula.

It’s a shame about the leap year issue - but the Integromat method seems to take care of it automatically.

Julian

1 Like