Skip to main content
Solved

Birthday formula

  • August 19, 2022
  • 6 replies
  • 105 views
D
Forum|alt.badge.img+5

Hi people!

I’m building a “Birthdays” table in my “Calendar” base and I’m looking for a formula that says:

if “Birthday” is within the next 2 months then display “soon”

What would this formula look like?

Best answer by JonathanB

Thanks Jonathan!

I’m still having a problem here. No errors from the formula but the formula labels ALL of the records as “Soon” in the formula field regardless of date.


Sorry! More complicated than I first realised. Try this:

IF(AND(SUM(DATETIME_FORMAT(TODAY(), "M")-DATETIME_FORMAT({Birthday}, "M"))<=2, SUM(DATETIME_FORMAT(TODAY(), "M")-DATETIME_FORMAT({Birthday}, "M"))>-1), "Soon")

This should work for any date given in {Birthday}, regardless of year.

6 replies

J
Forum|alt.badge.img+17
  • JonathanB
  • Inspiring
  • August 20, 2022

IF(DATETIME_DIFF(TODAY(), {Birthday}, "months")<=2, "Soon")

D
Forum|alt.badge.img+5

IF(DATETIME_DIFF(TODAY(), {Birthday}, "months")<=2, "Soon")


Thanks Jonathan!

I’m still having a problem here. No errors from the formula but the formula labels ALL of the records as “Soon” in the formula field regardless of date.

J
Forum|alt.badge.img+17
  • JonathanB
  • Inspiring
  • Answer
  • August 21, 2022

Thanks Jonathan!

I’m still having a problem here. No errors from the formula but the formula labels ALL of the records as “Soon” in the formula field regardless of date.


Sorry! More complicated than I first realised. Try this:

IF(AND(SUM(DATETIME_FORMAT(TODAY(), "M")-DATETIME_FORMAT({Birthday}, "M"))<=2, SUM(DATETIME_FORMAT(TODAY(), "M")-DATETIME_FORMAT({Birthday}, "M"))>-1), "Soon")

This should work for any date given in {Birthday}, regardless of year.

C
D
Forum|alt.badge.img+5

Sorry! More complicated than I first realised. Try this:

IF(AND(SUM(DATETIME_FORMAT(TODAY(), "M")-DATETIME_FORMAT({Birthday}, "M"))<=2, SUM(DATETIME_FORMAT(TODAY(), "M")-DATETIME_FORMAT({Birthday}, "M"))>-1), "Soon")

This should work for any date given in {Birthday}, regardless of year.


It works! but I have to filter out next years birthdays or it goes a bit haywire and can’t be filtered using the “Soon” field once the formula is used.

C
Forum|alt.badge.img+5
  • Comercial_Tesal
  • Participating Frequently
  • June 23, 2023

Hi everyone. I think I have the definitive formula based on the birthdate .

IF( DATETIME_DIFF( DATETIME_PARSE(CONCATENATE(YEAR(TODAY()), '-', DATETIME_FORMAT({Birthdate}, 'MM'), '-', DATETIME_FORMAT({Birthdate}, 'DD'))), TODAY(), 'days' ) < 0, DATEADD(DATETIME_PARSE(CONCATENATE(YEAR(TODAY()), '-', DATETIME_FORMAT({Birthdate}, 'MM'), '-', DATETIME_FORMAT({Birthdate}, 'DD'))), 1, 'year'), DATETIME_PARSE(CONCATENATE(YEAR(TODAY()), '-', DATETIME_FORMAT({Birthdate}, 'MM'), '-', DATETIME_FORMAT({Birthdate}, 'DD'))) )

 

Best regards

B
Forum|alt.badge.img
  • britthef
  • New Participant
  • October 11, 2023

Hi everyone. I think I have the definitive formula based on the birthdate .

IF( DATETIME_DIFF( DATETIME_PARSE(CONCATENATE(YEAR(TODAY()), '-', DATETIME_FORMAT({Birthdate}, 'MM'), '-', DATETIME_FORMAT({Birthdate}, 'DD'))), TODAY(), 'days' ) < 0, DATEADD(DATETIME_PARSE(CONCATENATE(YEAR(TODAY()), '-', DATETIME_FORMAT({Birthdate}, 'MM'), '-', DATETIME_FORMAT({Birthdate}, 'DD'))), 1, 'year'), DATETIME_PARSE(CONCATENATE(YEAR(TODAY()), '-', DATETIME_FORMAT({Birthdate}, 'MM'), '-', DATETIME_FORMAT({Birthdate}, 'DD'))) )

 

Best regards


Hello, this is great! But it's giving me a day behind. So if the birthdate is 09/30/1988, the formula is showing 09/29/2024 instead of the 30th. 

Terms & ConditionsAccessibility statement