Skip to main content

Calculation for birthday - off by 1 day or not date formatted

  • February 3, 2023
  • 5 replies
  • 46 views
T
Forum|alt.badge.img+4
  • tgrier
  • New Participant
  • 3 replies

I'm trying to create a date formula with the next/upcoming birthdate from a staff profile with Date of Birth. I need this to be formatted as a date so I can use it in a calendar, which has proven to be more difficult than I anticipated. Here are 2 ways I have gone about it. Any suggestions?

Attempt #1:

DOB is Staff member's Date of Birth:

IF(DOB = BLANK(),BLANK(), DATETIME_PARSE((((MONTH(DOB) & "/") & DAY(DOB)) & "/") & YEAR(TODAY())))
-the result is shy by 1 day. Birthdate of 4/12/__ is showing up as 4/11/23. It is recognized in Airtable as a date though. I just can't figure out why it fails. I believe it could be linked to timezone, but I don't have time on DOB or the formatting for the birthday field. Also, this really only creates birthdays for 2023. Some January birthdays are in the past and don't show up in 2024. Not a major issue but still problematic. So I tried the option below...
 
Option 2: Calculates correctly but I can't get it to be 'seen' as a date by Airtable:
IF(DATETIME_PARSE((((MONTH(DOB) & "/") & DAY(DOB)) & "/") & YEAR(TODAY())) < TODAY()
,
  DATETIME_FORMAT(DATEADD(Birthday, VALUE(ARRAYJOIN(1)), "years"), "M/D/YYYY")
,
  DATETIME_FORMAT((DATETIME_PARSE((((MONTH(DOB) & "/") & DAY(DOB)) & "/") & YEAR(TODAY()))), "M/D/YYYY"))
 
Any formula wizards have a suggestion here?
 

5 replies

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • 520 replies
  • February 3, 2023

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!

T
Forum|alt.badge.img+4
  • tgrier
  • Author
  • New Participant
  • 3 replies
  • February 3, 2023

Maybe something is buggy on my end, but it isn't working for me. See the options below and the resulting data:

  1. Fx 'Birthday' is the one that pumps out the day before the birthday
  2. 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)
  3. 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?

 

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • 520 replies
  • February 3, 2023

Maybe something is buggy on my end, but it isn't working for me. See the options below and the resulting data:

  1. Fx 'Birthday' is the one that pumps out the day before the birthday
  2. 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)
  3. 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()) ) )

 

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • 520 replies
  • February 3, 2023

Maybe something is buggy on my end, but it isn't working for me. See the options below and the resulting data:

  1. Fx 'Birthday' is the one that pumps out the day before the birthday
  2. 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)
  3. 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" ) )

 

 

 

K
Forum|alt.badge.img
  • kcs-it
  • New Participant
  • 1 reply
  • May 18, 2023

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()) ) )

 


Terms & ConditionsAccessibility statement