Date format error with formula field

1

I am new to Coda so my apologies if this is a rookie mistake.

I have a date field for birthdays with format of 31/12/2023. I have a second date formula field also with format 31/12/2023 to calculate the next birthday anniversary.

The formula I am using appears to be fit for purpose however i have a “Cannot convert value to the specified format” error.

From what I can tell the output is of type date but will not abide by the input column and/or column format. I also assumed the output was text and tried using Format() function to no avail - the output is the same.

The formula for the next birthday field:

thisRow.Birthday
  .Substitute(
    thisRow.Birthday.Year(), Today().Year()
  )
  .WithName(
    Next,
    If(
      Next > Today(),
      Next,
      thisRow.Birthday
        .Substitute(
          thisRow.Birthday.Year(), Today().Year() + 1
        )
    )
  )

Below is the result showing errors. There appears to be a mismatch between the configured column format and the formula result format. The result is in the correct format but interpreted incorrectly as a different (Nth American) format.

image

Is this a known bug or is my approach wrong?

Thank you,

2

I would try creating a new Date() object instead of treating it as a text with Substitute :slight_smile:

1 Like
3

Thank you so much. That worked nicely.

If(
  Date(Today().Year(), thisRow.Birthday.Month(), thisRow.Birthday.Day()) >
    Today(),
  Date(Today().Year(), thisRow.Birthday.Month(), thisRow.Birthday.Day()),
  Date(Today().Year() + 1, thisRow.Birthday.Month(), thisRow.Birthday.Day())
)
2 Likes
4

hi @Nicholas_Keown ,

You implemented the suggestion of @Rickard_Abraham very well, the solution I suggested using WithName works with the USA formatting, and only with the USA formatting (I tested it as well). This one works with any format and that is what we need.

You may consider to rename your question as next birthday with EU formatted dates or something similar. Others will then easier find this logic.

Thanks for your contribution, cheers, Christiaan

5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.