Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 31, 2020 06:01 AM
Hello, I would really appreciate some help here. I have a big list of client birthdays, the majority of them are partial dates (DD/MM), but some are complete dates (DD/MM/YYYY). Now, I have 3 fields:
Date of birth
This is a single line text field because I can’t enter partial dates in a date field
Age
This is a formula field I use to get their age when I do have their full date of birth
IF({Date of birth}, DATETIME_DIFF(TODAY(), {Date of birth}, ‘years’))
Birthday
This is a formula field I’m using to strip away the year of complete dates and make both, partial and complete dates, recurring dates that I can see in the calendar view
IF({Date of birth}, DATETIME_PARSE(DATETIME_FORMAT({Date of birth},‘MM/DD’)&"/"&YEAR(NOW())))
The problems I’m having are the following:
We use the European date format (DD/MM/YYYY) here, but when I enter a partial date in the date of birth field (single line text field), say, 1/6 (June 1st), the birthday field (date field, European date format ) gives me 6/1/2020 (January 6), so I have 2 fields with different date formats, which gets extremely confusing at times. Is there any way I can make this not be the case?
I want the age field to show me their age only when I have a complete date of birth (DD/MM/YYYY) and to show me nothing when it’s a partial date (DD/MM), because, at the moment, when I enter a complete date it’s all well and good, but when I enter a partial date it shows me some random number I can’t make any sense of, and I just can’t get rid of it
Thank you so much in advance. :bowing_man:
Solved! Go to Solution.
May 31, 2020 12:40 PM
For your {Birthday}
field, you can adjust your formula to:
IF({Date of birth}, DATETIME_PARSE({Date of birth}, 'DD/MM' & '/' & YEAR(NOW())))
^ this will parse your format properly
As for the {Age}
field, you can adjust the formula to:
IF({Date of birth},IF(LEFT(RIGHT({Date of birth},5),1)="/", DATETIME_DIFF(TODAY(), DATETIME_PARSE({Date of birth}, 'DD/MM/YYYY'), 'years')))
^ and this will check if the {Date of Birth}
field ends in a year in an admittedly round-about fashion.
Let me know if this works for you. If so, please mark it as the solution.
May 31, 2020 12:40 PM
For your {Birthday}
field, you can adjust your formula to:
IF({Date of birth}, DATETIME_PARSE({Date of birth}, 'DD/MM' & '/' & YEAR(NOW())))
^ this will parse your format properly
As for the {Age}
field, you can adjust the formula to:
IF({Date of birth},IF(LEFT(RIGHT({Date of birth},5),1)="/", DATETIME_DIFF(TODAY(), DATETIME_PARSE({Date of birth}, 'DD/MM/YYYY'), 'years')))
^ and this will check if the {Date of Birth}
field ends in a year in an admittedly round-about fashion.
Let me know if this works for you. If so, please mark it as the solution.
May 31, 2020 02:30 PM
Here’s similar approach with slightly different formulas. In these formulas, days and months can be either one digit or two digits.
The key to parsing dates in European format is to use the format specifier in DATETIME_PARSE
.
You can look a the length of the string to to see if there is a year or not.
If there is no year, the string could be from 3 characters to 5 characters.
If there is a year, the string will always be over 5 characters long.
IF(LEN({Date of birth}) > 5, "has year")
If there is a year, use DATETIME_DIFF, and parse the date using the European format.
IF(
LEN({Date of birth}) > 5,
DATETIME_DIFF(
TODAY(),
DATETIME_PARSE({Date of birth}, 'D/M/YYYY'),
'years'
)
)
If there is a year, strip off the year (last 4 characters) and tack on the year for today.
If there is no year, simply tack on the year for today. Parse the result as a date in European format.
Be sure to set the formatting options to format this date in European order. You may also need to turn on the Use the same time zone (GMT) for all collaborators toggle* or your dates may be off a day, depending on the time and your timezone.
IF(
{Date of birth},
IF(
LEN({Date of birth}) > 5,
DATETIME_PARSE(
LEFT({Date of birth}, LEN({Date of birth}) - 4) & YEAR(TODAY()),
'D/M/YYYY'
),
DATETIME_PARSE({Date of birth} & "/" & YEAR(TODAY()), 'D/M/YYYY')
)
)
May 31, 2020 02:39 PM
This formula is really neat. I hadn’t seen this usage before. Can you explain how it works?
Does this part of the formula work by checking if the fifth character from the end is a /
character?
May 31, 2020 02:54 PM
To clarify, both our approaches handle two-digit months/days.
DATETIME_PARSE()
and DATETIME_FORMAT()
are pretty lax when it comes to stringing in TODAY()
or NOW()
values as part of the format. I tend to do things this way just to get a shorter formula.
Correct. Your solution to this part of the problem is cleaner, though. Logically, if the date included a year the firth-from-the-right character would need to be a “/” (my approach), but even more logically: if the date included a year the date would need to be more than five characters long (yours).
May 31, 2020 03:18 PM
Absolutely! Sorry, I didn’t mean to imply that yours didn’t. As you can see from my screen capture, I took a round-about way of trying to figure out the day and month individually, which meant that I had to figure out how to deal with one and two digit days and months. Of course, for this particular use case, the individual day and month don’t matter.
I think it is really neat to see different ways of doing things. There are so many ways of doing things in code, and always more to learn. This time I got to learn from you how to splice in the current year into DATETIME_PARSE. I can see this trick being really useful.
Thanks for letting me know that I understood your method of determining if there is a year. Someone once told me that being able to read code was as important as being able to write code. I don’t like slogging through lots of code, but a nice little snippet can be fun.
Jun 01, 2020 04:38 AM
OMG, you guys are lifesavers! I don’t understand what’s going on in Kamille’s second formula, but everything works perfectly. Thank you so much!
Jun 01, 2020 10:08 AM
The second formula does the following, in order:
{Date of birth}
field filled out? If so, continue. If not, show a blank cell.{Date of birth}
a “/” (meaning that means the cell contains a year and thus is a complete date)? If so, get the difference in years between today and the {Date of birth}
(which is formatted to the correct DD/MM order using DATETIME_PARSE()
.@kuovonne’s formula for {Age}
is a bit more efficient, largely the same except checking whether the date includes a year was simplified from LEFT(RIGHT({Date of birth},5),1)="/"
to LEN({Date of birth}) > 5