DATETIME_FORMAT & Cardinal #s

[Joel_Smith]

I’ve imported a simple Excel spreadsheet into Airtable. In that spreadsheet were 2 columns:
• Day & Month expressed as a number

THE GOAL
Create a date field that can be used in a calendar.

THE PROBLEM
However, once I import those into Airtable and make the field type a #, Airtable removes gives me a 1 for Jan., 2 for Feb, etc. However, when I combine Month, Day, Year into a new Date field, Jan 7 becomes 172018, but it needs to be 01072018. (See attached)

I’VE TRIED
I’ve tried using DATETIME_FORMAT and MM for Month and DD for Day so I have Cardinal #s with 2 digits, but I’m doing something wrong. I’ve spent hours on the boards and reading Airtable support, but I’m at a loss.

I’m sure there’s a simple solution, but I’m not sure what question to ask. :winking_face:

Can anyone point me in the right direction?

Thanks!

[W_Vann_Hall]

Try this for your {Date} formula:

DATETIME_PARSE(Month&' '&Day&' '&Year,'M D YYYY')

That should give you a valid date field, albeit with the time of day set to midnight. You can append a different timestamp if you wish or configure formatting of {Date} to remove the timestamp.

Airtable

Date Parse test - Airtable

Explore the "Date Parse test" base on Airtable.

(In the test base, above, the primary field, which simply duplicates {Date}, is formatted to remove the timestamp.)

[Joel_Smith]

That works Vann. Thanks!