+3I have lots of dates that are setup as numbers in my excel sheet that I am bringing into airtable. I need to change the formatting of the dates while leaving them as numbers in the original column.
Example. 20170112 YYYYDDMM I need it converted to 12-01-2017 MM-DD-YYYY
I usually convert it using the following excel formula:
=DATE(LEFT(CELL1,4),MID(CELL1,5,2),RIGHT(CELL1,2))
Thanks for any help you can provide.
+18I first collect the Day-Month-Year in seperate fields, just for making it visual.
You start off with a number
It is not a date, and when you check that with a formula DATETIME_FORMAT(NR,‘MM-DD-YYYY’) you will get an error message.
So let’s just split the date into 3 parts, like you described yourself:
DAY(MID(NR,5,2))
MONTH(RIGHT(NR,2))
YEAR(LEFT(NR,4))
Now you can use CONCATENATE() to collect the 3 into a datestring:
CONCATENATE(Month,"-",Day,"-",Year)
And then use the formula DATETIME_FORMAT(DateComp,‘MM-DD-YYYY’) to actually create a datefield
When you check the date now, for example with finding the WEEKDAY(Date) it will actually show you the weekday.
You have a date!
+3I first collect the Day-Month-Year in seperate fields, just for making it visual.
You start off with a number
It is not a date, and when you check that with a formula DATETIME_FORMAT(NR,‘MM-DD-YYYY’) you will get an error message.
So let’s just split the date into 3 parts, like you described yourself:
DAY(MID(NR,5,2))
MONTH(RIGHT(NR,2))
YEAR(LEFT(NR,4))
Now you can use CONCATENATE() to collect the 3 into a datestring:
CONCATENATE(Month,"-",Day,"-",Year)
And then use the formula DATETIME_FORMAT(DateComp,‘MM-DD-YYYY’) to actually create a datefield
When you check the date now, for example with finding the WEEKDAY(Date) it will actually show you the weekday.
You have a date!
Thank You That worked for me.
+17An alternate method is to use the function DATETIME_PARSE
In your case, you’d put:
DATETIME_PARSE(NR,'YYYYDDMM')
Which will give you the date, formatted by default as M/D/YYYY h:mm a according to the documentation.
+18An alternate method is to use the function DATETIME_PARSE
In your case, you’d put:
DATETIME_PARSE(NR,'YYYYDDMM')
Which will give you the date, formatted by default as M/D/YYYY h:mm a according to the documentation.
Good you mention that.
Combining PARSE + FORMAT would be:
DATETIME_FORMAT(DATETIME_PARSE(NR,‘YYYYDDMM’),‘MM-DD-YYYY’)
I never used PARSE a lote, but it really is useful!
Thx
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.