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(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?
Best Answer
-
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.
Answers
-
is the format D/MM/YY ?
days 1....9?
-
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...
-
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.
-
That worked! Thank you so much
-
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!
-
What exactly is your date format?
mm/dd/yy
or
dd/mm/yy
-
mm/dd/yy
-
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)))
-
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)))
-
@rlacout If you are pulling from the system generated Created (date) type column, you should only need to use
=DATEONLY(Created@row)
Help Article Resources
Categories
Check out the Formula Handbook template!
