Converting text to date

Try this

IF(
   {Day-Month Field},
   DATETIME_PARSE(
      {Day-Month Field} & " " & YEAR(TODAY()),
      'D MMMM YYYY'
   )
)

The date in this field will change when the year changes, such that it always shows the current year (ie, right now it will show 2018 in every field, but in 12 days, it will start to show 2019 in every field). Not sure if that’s what you wanted or not.

ok i will try your formula.
Thank you

it quite works, i mean, i’m italian and in that field, called “data” the text is ‘22 ottobre’ that means ’ 22 october’. Ok with eng months works, with ita ones no… damn i had to do a conversion…

You can specifiy a locale when using DATETIME_PARSE, which should handle non-US date formats. According to the documentation for SET-LOCALE, adding "it" as the final argument should do the trick.

thank you Martin, now it works as intended, i’ve just one thing more to solve,
can i set an auto sort for the table based on a field?
I’ve seen that sort is possible, but every time i add a record i have to manually sort again.
I hope i’ve explained well.

Robert, this is not currently available in Airtable, but I can tell you that auto-sort is currently being beta-tested. It works well, and is an optional setting. I have not experienced any problems with it in beta testing, so I anticipate that they will probably roll it out at some point in the future.

However, that’s not a guarantee – beta features are never guaranteed. It could be that they decide to scrap the feature.

In the meantime, and in the case that they choose to scrap the feature, a way to quickly reapply your sorting rule after adding a new record is to press ctrl/⌘ + shift + S and then quickly hit enter. This opens the Sort menu where your sort rules are defined, and when it opens the “Apply” button will be highlighted/selected, so hitting enter will apply your sorting rules fresh.

Thank you Jeremy but is not useful in my case, you see i’m using airtable as a base in which i add records by voice from alexa using Voiceflow Creator. I dont have a keyboard.
I hope that this feature will be released soon.

Hi @Roberto_Ceccherini

You could achieve this using either Zapier or Integromat (the latter is probably less expensive and also more powerful in many ways).

The only downside is that the values wouldn’t update immediately - how often these processes run depends on your licence level. You could test it out with a free Integromat subscription though.

I hope this helps!

hi Julian, i don’t know is this can help cause i don’t know Zapier or Integromat, are something similar to airtable? are integrations of airtable?

IF(
   {Day-Month Field},
   DATETIME_PARSE(
      {Day-Month Field} & " " & YEAR(TODAY()),
      'D MMMM YYYY'
   )
)

Hey there Jeremy,

I’m looking to do something really similar to this, but not sure if it’s even possible.

Let’s say for example that I have a field called {Due Date} but is only populated with text or a # (Not sure if either are possible) , anywhere from 1-31 (or 1st-31st). Is it possible to create a formula field that will take this number, and convert it to the next possible date that is available?

Example: Today is the 13th. The Due Date for a record is “9” or “9th”.
The goal is for the formula field to populate the next upcoming “9th” - which for examples sake would be “09/09/2019”.

Or if it were within the same month and coming up, to populate the current month of course.

I’m using airtable to track my monthly budget, outstanding debts, payment methods, income sources, income, expenses, and monthly reports (works quite well so far). I’m just trying to add a few more features if possible.

To hopefully put it simply: I’m trying to find a way to use a text or # field and populate the next upcoming due date in “MM/DD/YYYY” format. Is this possible?

Hi @Dakota-Bushnell,

This formula should do it:

IF(
    {Due Date},
    IF(
        IS_AFTER(
            DATETIME_PARSE(MONTH(TODAY()) & "/" & VALUE({Due Date}) & "/" & YEAR(TODAY())),
            DATEADD(TODAY(),-1,'day')
        ),
        DATETIME_PARSE(MONTH(TODAY()) & "/" & VALUE({Due Date}) & "/" & YEAR(TODAY())),
        DATEADD(
            DATETIME_PARSE(MONTH(TODAY()) & "/" & VALUE({Due Date}) & "/" & YEAR(TODAY())),
            1,
            'month'
        )
    )
)

I tested it with {Due Date} as a number field where you just put in a number for the day of the month the bill is due:

It can also work with {Due Date} as a text field with just a number for the day of the month, OR with an ordinal (1st, 2nd, 27th, etc):

It will not work with all text ordinals, however (first, second, twenty-seventh, etc) - it requires an integer to operate on.

I also tested it against a new year, and it should successfully convert a due date of “1st” to 1/1/2020 when TODAY() is 12/2/2019 or later.

EDIT:
One last thing…
Due to Airtable’s quirkiness with converting dates referenced out of other fields, I would suggest checking the “Use the same time zone…” option in the formatting tab for your {Next Due} field:

IF(
    {Due Date},
    IF(
        IS_AFTER(
            DATETIME_PARSE(MONTH(TODAY()) & "/" & VALUE({Due Date}) & "/" & YEAR(TODAY())),
            DATEADD(TODAY(),-1,'day')
        ),
        DATETIME_PARSE(MONTH(TODAY()) & "/" & VALUE({Due Date}) & "/" & YEAR(TODAY())),
        DATEADD(
            DATETIME_PARSE(MONTH(TODAY()) & "/" & VALUE({Due Date}) & "/" & YEAR(TODAY())),
            1,
            'month'
        )
    )
)

This was 100% what I was looking for!

Thank you very much for taking the time to help me out!