Help me please (partial dates)

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.

Determining if there is a year

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

Getting the age

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'

  )

)

Getting the birthday for this year

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

  )

)

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?

IF(LEN({Date of birth}) > 5, "has year")

IF(

  LEN({Date of birth}) > 5,

  DATETIME_DIFF(

    TODAY(), 

    DATETIME_PARSE({Date of birth}, 'D/M/YYYY'),

    'years'

  )

)

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

  )

)

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

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.

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!

The second formula does the following, in order:

1. Is the {Date of birth} field filled out? If so, continue. If not, show a blank cell.


2. Is the fifth character from the right in the {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