Hey @tgrier - I normally type more, but I'm tired, so I apologize for the brevity.
I was in the middle of formatting the formulas you provided when I realized something.
Why is the DOB field not a date field?
Regardless, here's a more streamlined formula for you:
IF(
{DOB},
DATETIME_PARSE(
{DOB}, "DD/MM/" & YEAR(TODAY())
)
)
You'll want to confirm that your field's configuration is set correctly.
Whenever you're seeing a discrepancy between your dates, it's almost always because of your time zone settings.
Here are the configuration settings for my formula field from the screenshot above:
Lemme know if it throws you any errors and I'd be happy to troubleshoot it a bit!
Maybe something is buggy on my end, but it isn't working for me. See the options below and the resulting data:
- Fx 'Birthday' is the one that pumps out the day before the birthday
- Fx 'Birthday copy' is the formula you've provided me (and I'm not even sure I can understand how Airtable is getting to what it gets to)
- Fx 'Next Bday' looks great in the table, but can't be seen as a date for calendar purposes.
I'm wondering if the data you've provided points to something. The DOB in your data is formatted as a single text? Could the be causing the difference between your result and mine?
Maybe something is buggy on my end, but it isn't working for me. See the options below and the resulting data:
- Fx 'Birthday' is the one that pumps out the day before the birthday
- Fx 'Birthday copy' is the formula you've provided me (and I'm not even sure I can understand how Airtable is getting to what it gets to)
- Fx 'Next Bday' looks great in the table, but can't be seen as a date for calendar purposes.
I'm wondering if the data you've provided points to something. The DOB in your data is formatted as a single text? Could the be causing the difference between your result and mine?
I assumed that you were using a string value for the DOB field for one reason or another.
If you're (correctly) using a date field, you can use this formula:
IF(
{DOB Date},
DATETIME_PARSE(
DAY({DOB Date}) & "/" & MONTH({DOB Date}), "DD/MM/" & YEAR(TODAY())
)
)
Maybe something is buggy on my end, but it isn't working for me. See the options below and the resulting data:
- Fx 'Birthday' is the one that pumps out the day before the birthday
- Fx 'Birthday copy' is the formula you've provided me (and I'm not even sure I can understand how Airtable is getting to what it gets to)
- Fx 'Next Bday' looks great in the table, but can't be seen as a date for calendar purposes.
I'm wondering if the data you've provided points to something. The DOB in your data is formatted as a single text? Could the be causing the difference between your result and mine?
As a side note... if you'd like to return a date value for the Next Birthday field, you can use this formula:
IF(
{DOB Date},
DATEADD(
DATETIME_PARSE(
DAY({DOB Date}) & "/" & MONTH({DOB Date}), "DD/MM/" & YEAR(TODAY())
),
1,
"year"
)
)
This is based off @Ben_Young1's solution. I don't know why the function subtracts a day, it doesn't seem to be a timezone issue. I used DATEADD() to add a day. Hopefully it'll work for you.
IF(
{DOB},
DATETIME_PARSE(
DAY(DATEADD({DOB},1,'days')) & "/" & MONTH(DATEADD({DOB},1,'days')), "DD/MM/" & YEAR(TODAY())
)
)
