Help

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

Topic Labels: Formulas
Solved
Jump to Solution
1011 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Troy_Gamble
6 - Interface Innovator
6 - Interface Innovator

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
1 Solution

Accepted Solutions
Troy_Gamble
6 - Interface Innovator
6 - Interface Innovator

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

See Solution in Thread

4 Replies 4
kuovonne
18 - Pluto
18 - Pluto

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.

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'

)

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
6 - Interface Innovator
6 - Interface Innovator

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