Coda Date type does not recognize textual months

[Bob_Day]

We have a workflow that pushes email content into Coda. We need the dates of the original emails, not ‘creation date’ and so we use one of the email headers for that. Unfortunately, each SMTP service uses its own date stamp. We are wrestling with dates in the form of “January DD…”. In order to parse these into a Coda date type, the Month name needs to be replaced with Month number, and there’s no function for that. I’m hoping that someone has a more simple way of converting other than writing a Pack. This is one I’ve come up with so far:

The following parses “June 7, 2022 at 5:27:25 PM PDT” into ‘2022-06-07 5:58 PM’ (with YYYY-MM-DD formatting)

If(IsDate(thisRow.date),thisRow.date ,
  Concatenate(
    20,
    RegexReplace(thisRow.date,"^.*?, 20(\d{2}).*$" , "$1"),
    "-",
    switch(true,
      regexmatch(thisRow.date,"Jan|January"),"01",
      regexmatch(thisRow.date,"Feb|February"),"02",
      RegexMatch(thisRow.date,"Mar|March"),"03",
      RegexMatch(thisRow.date,"Apr|April"),"04",
      RegexMatch(thisRow.date,"May"),"05",
      RegexMatch(thisRow.date,"Jun|June"),"06",
      RegexMatch(thisRow.date,"Jul|July"),"07",
      RegexMatch(thisRow.date,"Aug|August"),"08",
      RegexMatch(thisRow.date,"Sep|September"),"09",
      RegexMatch(thisRow.date,"Oct|October"),"10",
      RegexMatch(thisRow.date,"Nov|November"),"11",
      RegexMatch(thisRow.date,"Dec|December"),"12"
    ),
    "-",
    RegexReplace(thisRow.date,"^\w{3,9} (\d+).*$" , "$1"),
    RegexReplace(thisRow.date,"^.*? at(.* ).*$" , "$1")
  )
)

Interestingly, there is a “January 21, 2022” date format, but Coda doesn’t seem to recognize textual dates as a date type.

The above works for our particular use case, but only for the particular format passed.

Any thoughts appreciated.

[Ryan_Martens2]

I just did some testing and it looks like the Date column format accepts a wide variety of text dates including “January 21, 2022”. I also noticed that IsDate(textDate) predicts whether or not the Date column format will successfully convert it.

Notable Exception: except in the case of integers, where IsDate("5.5") returns False but the date formula will convert it to Jan 4, 1900 12pm. ie, 5.5 days since Dec 30, 1899.

Maybe it’s the “at” that’s tripping you up?

Note IsDate("June 7, 2022 5:27:25 PM PDT") ⇒ True

If that doesn’t work for you, maybe you could share a dummy doc demonstrating the issue.

[paul_p]

@Bob_Day a few thoughts here!

First, similar to @Ryan_Martens2 's note above, with some very minor editing, you can convert these strings with Coda toDateTime function! Specifically, you just need to replace the “at” with “”.

Second, to the note about Coda not having a month name to month number function (surprising!), you can hack this pretty easily - specifically, try something like month(todate(“June 1, 2022”)), which should return 6.

Third, for any kind of date processing from strings like this, split_part functions can be really helpful! Example: date_string.split(" ").nth(1) would extract all the month names from the list above, so you could avoid that long switch statement.

Attaching some images below in case they’re helpful!

[paul_p]

Full function required to deal with this particular date string then would be: ToDateTime(date_string.substitute(“at”, “”))! Add that as a formula in a new datetime column, and then you have some additional formatting options as well (eg choosing YYYY-MM-DD). Last couple screenshots below: