Converting text to date (M/D) format issues.

[Troy_Gamble]

Working with birthdays where I only know the client's Month/Day and not their Year of birth.  So text field is 5/12 for example but I'd like to have an actual date of 5/12 so I can set up reminders 5 days before the actual birthday.  I used the following formula but it's not working correctly.  

DATETIME_PARSE(Birthday, 'MM/DD')

Thank you in advance

[kuovonne]

DATETIME_PARSE() needs a year to parse. You can hard code a specific year or the current year.

DATETIME_PARSE(
  Birthday & '/' & YEAR(TODAY()), 
  'MM/DD/YYYY'
)

Note that if you use DATEADD() with the result to subtract 5 days, you will have issues with the first few days in January. So you will need to check for those cases and adjust the formula to account for the change in year. But that is getting more than I want to type on my phone where I can see only 3-4 narrow lines of text at a time.

If some of your text fields do have a year, you will also need to adjust the formula to use only the month and day from the text field, and not the year.

Also, I usually avoid using TODAY() and NOW() as they can be resource intensive if you have a lot of records. But TODAY() is not as problematic as NOW(), so you might be okay.

[Troy_Gamble]

Thank you for  your reply!  I'm a new Airtable user.  When I copied and pasted your formula provided, it said it wasn't valid. I made a small adjustment and it was accepted but now it's changing the day by a day. 

Here's what I input...sure I did something wrong!

DATETIME_PARSE(

  Birthday & YEAR(TODAY()), 

  'MM/DD/YYYY'

)

[kuovonne]

Looks like some curly quotes snuck in to my formula when I was typing on my phone. 

When date formulas are off by a day, it is usually a timezone issue. In this particular case, because you are parsing a text field to get a date, set the formula to use GMT.

[Troy_Gamble · Accepted solution]

Thanks so much....the time zone was the issue.  I really appreciate you taking the time to help me.  Cheers