Help me please (partial dates)

[Peter_Baumann1]

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:

1. 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?

2. 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:

[Kamille_Parks · Accepted solution]

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.

[kuovonne]

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

[kuovonne]

Kamille_Parks:

DATETIME_PARSE({Date of birth}, ‘DD/MM’ & ‘/’ & YEAR(NOW()))

This formula is really neat. I hadn’t seen this usage before. Can you explain how it works?

Kamille_Parks:

IF(LEFT(RIGHT({Date of birth},5),1)="/", …

Does this part of the formula work by checking if the fifth character from the end is a / character?

[Kamille_Parks]

kuovonne:

In these formulas, days and months can be either one digit or two digits.

To clarify, both our approaches handle two-digit months/days.

kuovonne:

This formula is really neat. I hadn’t seen this usage before. Can you explain how it works?

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.

kuovonne:

Does this part of the formula work by checking if the fifth character from the end is a / character?

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

[kuovonne]

Kamille_Parks:

To clarify, both our approaches handle two-digit months/days.

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.

[Peter_Baumann1]

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!

[Kamille_Parks]

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