Formula / calculating anniversary

Technical Help
, ,
1

Hi,

I’m trying to calculate the next anniversary, based on a date field (e.g. a birthday). So today is 2022-12-27. If someone has a birthday of 1992-05-03, his next anniversary would be 2023-05-03.

Now this requires something like a now() or today() function. I have read a post that this function is not there yet (see Date calculation with Formula field). So I’ll settle for that until that is resolved (this exists in Airtable).

The next thing I tried is using filters. The end goal is to connect to a messaging service like Zapier (though I’m using NodeRED) through the API. The problem here is, however, that I fail to find a filter that does this, based on the reference date (like the birthday). There are some ‘ago’ filters, so the concept of now() exists there, but there is no filter that seems to give me what I want.

The third thing I tried is to filter on the receiving end of the API call. This works fine, except for Baserow providing paginated results, so this makes filtering on the receiving end very cumbersome / messy. I think I would know how to do this in NodeRED but I don’t really want to go there for - I hope - obvious reasons.

Any ideas? I sort of like the idea I have in Airtable now, where ‘Date’ is the date of the event like a birthday, and the result of the function is the next/upcoming anniversary.

IF({Date}!="",DATEADD({Date},(DATETIME_DIFF(DATEADD(TODAY(),-1,‘Days’),{Date},‘Years’)+1),‘Years’))

I guess I’ll have to wait for that NOW/TODAY() function.

2

Aquí hay un ejemplo de cómo podría hacerse esto usando la biblioteca de fechas de NodeJS:

const moment = require('moment');

// Define la función para calcular el próximo aniversario
function nextAnniversary(birthday) {
  // Obtiene la fecha actual
  const today = moment();
  
  // Calcula el número de años desde el cumpleaños hasta la fecha actual
  const yearsSinceBirthday = today.diff(birthday, 'years');
  
  // Agrega el número de años a la fecha de cumpleaños para obtener la fecha del próximo aniversario
  const nextAnniversary = birthday.add(yearsSinceBirthday + 1, 'years');
  
  return nextAnniversary;
}

// Prueba la función
const birthday = moment('1992-05-03');
console.log(nextAnniversary(birthday).format('YYYY-MM-DD')); // Debería mostrar 2023-05-03
3

Thank you @Diagonal, I had a solution without moment, so thanks for this one.

This is still at the receiving end (for me, NodeRED), after the query. I suppose if I would like to have this as column in Baserow, or as filter, I would need to modify the codebase.

I suppose the wait is for this now()/today() function.

2 Likes
4

Now() and today() functions will be added soon, they are on the roadmap for 2023. In 2 weeks or so, we may know more details on when to expect the feature to be released.

3 Likes