Skip to main content

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?


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

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.

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.

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.

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

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. 

Reply


Terms & Conditions