Convert drop down in text format to date format

I have seen various other questions in the community, but I can't quite get my equation there, I am trying to convert a drop down from a form to a date in the adjacent column. All dropdown items are formatted as follows:

7/07/20

Here is what I am trying:

date function.docx

=DATE(VALUE("20" + RIGHT([Testing Date]@row, 6, 7)), VALUE(MID([Testing Date]@row, 3, 4)), VALUE(LEFT([Testing Date]@row, 1)))

any thoughts on why I can't get this to work?

Find more posts tagged with

Formulas

functionality

Accepted answers

Paul Newcome

There are a few issues. The first would be where you are pulling the year with the RIGHT statement. You only want to pull two digits, not seven.

RIGHT([Testing Date]@row, 2))

The next issue is that your formula will only work for single digit months. The easiest fix would be to reformat the dropdown values as you mentioned above to always have two digits. Then you would use

LEFT([Testing Date]@row, 2) for the month and

MID([Testing Date]@row, 4, 2) for the day.

All comments

Joachim Mund

is the format D/MM/YY ?

days 1....9?

brian25736

Joachim...thanks for response, in terms of the drop down column, its text right now as follows:

5/5/2020

7/5/2020

With that being said, I could edit those drop down values to say 05/01/20 etc., whatever might make it easier...

Paul Newcome

There are a few issues. The first would be where you are pulling the year with the RIGHT statement. You only want to pull two digits, not seven.

RIGHT([Testing Date]@row, 2))

LEFT([Testing Date]@row, 2) for the month and

MID([Testing Date]@row, 4, 2) for the day.

brian25736

That worked! Thank you so much

brian25736

Paul, you helped me on this and I am so grateful, but I am having a problem still...when I tested the formula, the date was 07/07/20, and the formulate below worked great for that...but then when I had a date come in of 08/04/20, the formula is swapping the mid and left side so that its formatting 08/04/20 to 04/08/20 in the date column...so its swapping them for some reason...here is my formula:

=DATE(VALUE("20" + RIGHT([Testing Date (Form)]@row, 2)), VALUE(MID([Testing Date (Form)]@row, 4, 2)), VALUE(LEFT([Testing Date (Form)]@row, 2)))

So for 05/05/20 it would be fine, but for say 06/02/20 it would translate that to 02/06/20

it highlights that this is actually backwards somehow, but I can't figure out how to fix it

Any help would be greatly appreciated!

Paul Newcome

What exactly is your date format?

mm/dd/yy

dd/mm/yy

brian25736

mm/dd/yy

Paul Newcome

Ok. Let's switch it up a little.

=DATE(VALUE("20" + RIGHT([Testing Date (Form)]@row, 2)), VALUE(LEFT([Testing Date (Form)]@row, 2)), VALUE(MID([Testing Date (Form)]@row, 4, 2)))

rlacout

I'm having the same issues with the system column for "DATE CREATED" to only show the date, not the time. Here is my formula that I get #INVALID VALUE as a result. What is the (Form) used for in the above post? I tried with & without the (Form) and neither worked.

=DATE(VALUE("20" + RIGHT(Created@row, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2)))

Paul Newcome

@rlacout If you are pulling from the system generated Created (date) type column, you should only need to use

=DATEONLY(Created@row)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.