Converting text to date

[Roberto_Ceccher]

Hi all, i have a field with a text like this " 1 april".
Can it be converted into 01/04 plus current year?
ty all

[Dakota-Bushnell]

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?

[Jeremy_Oglesby]

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:

[Dakota-Bushnell]

This was 100% what I was looking for!

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