Text to Date Field

Pam Ferguson

I have a file that will be automatically uploaded from another source, but the vendor has the date input as text in the following format: 20200113. Is there anyway that I can easily get this information into an actual Date column so that I can report off of it? Any suggestions would be greatly appreciated!

Mark Safran

If you import that "20200113" into a Text/Number column, you can then create an adjacent Date-formatted column and use this formula (wherein [Imported Date] is the name of the column where the raw "20200113" gets put):

=DATE(VALUE(LEFT([Imported Date]@row, 4)), VALUE(MID([Imported Date]@row, 5, 2)), VALUE(RIGHT([Imported Date]@row, 2)))

-MS

Mark Safran

If you import that "20200113" into a Text/Number column, you can then create an adjacent Date-formatted column and use this formula (wherein [Imported Date] is the name of the column where the raw "20200113" gets put):

=DATE(VALUE(LEFT([Imported Date]@row, 4)), VALUE(MID([Imported Date]@row, 5, 2)), VALUE(RIGHT([Imported Date]@row, 2)))

-MS

Pam Ferguson

Thank you so much! That worked perfectly!

Colin Harken

Hi

On a related problem i have dates in a primary column with the following syntax 16/12/2020 that i need to convert into a date column.

Can someone please assist.

Thanks

Genevieve P.

Hi @Colin Harken

I found a formula that @Mark Cronk created on this post, here: Converting a drop down single select list of dates into a date I can use with Calendar App

=DATE(VALUE(RIGHT([date text]@row, 4)), VALUE(LEFT([date text]@row, 2)), VALUE(MID([date text]@row, 4, 2)))

You'll nee to update the column name in [these] with your primary column name. Let us know if this works for you!

Cheers,

Genevieve