Change Date format from a formula

jrv924

New Member
Hello,

This might be confusing, but I am at a loss. I had some major help creating a formula to show the date on a different number (Other Post), but I am trying to do this for a different serial number we use for warranty verification. This one has a different format for the day: 160206 (yy/mm/dd). I was able to break apart using the formulas in the other post, but I am trying to make the date go from: 160206 to 02/06/2016 with a formula already in the field. Any assistance would be appreciated!

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Joe4

Like this?
Excel Formula:
``=MID(A1,3,2) & "/" & RIGHT(A1,2) & "/20" & LEFT(A1,2)``

Rick Rothstein

MrExcel MVP
Here is another formula that you can try...
Excel Formula:
``=TEXT(MID(A1&A1,3,6),"00\/00\/2\000")``

jrv924

New Member
As I am learning this, I came up with this formula:

=MID(A6,8,2) & "/" & MID(A6,10,2) & "/20" & MID(A6,6,2)

For what I need, it breaks it down and organizes exactly how I want it to look. I appreciate both of you for your help as it helps me learn formulas!

Thanks.

Justin

Joe4

As I am learning this, I came up with this formula:

=MID(A6,8,2) & "/" & MID(A6,10,2) & "/20" & MID(A6,6,2)

For what I need, it breaks it down and organizes exactly how I want it to look. I appreciate both of you for your help as it helps me learn formulas!

Thanks.

Justin
It appears that you are answering a different question than you posed in this thread.
That formula does not seem to work on the sample data you provided in your initial post (160206).

jrv924

New Member
I apologize, but I changed my formatting around what I wanted to do and did not clarify - which my mistake. I guess I was too excited to figure this out and forgot to mention that. Sorry!!! I took the original serial number for that specific item: 2280516020601310

Typing this: =MID(A6,8,2) & "/" & MID(A6,10,2) & "/20" & MID(A6,6,2)

Which got me the required results I wanted: 02/06/2016

Sorry for any confusion...

Justin

Joe4

What makes it very confusing is that if you mark a post as the solution, it shows right under the original question.
So if someone reads the original question, and then the solution, it won't make any sense (as the solution does not really pertain to the original question asked).

I see that you now marked it with a solution that pertains to your original question.
Thank you.

jrv924

New Member
What makes it very confusing is that if you mark a post as the solution, it shows right under the original question.
So if someone reads the original question, and then the solution, it won't make any sense (as the solution does not really pertain to the original question asked).

I see that you now marked it with a solution that pertains to your original question.
Thank you.

I saw that and changed it around. I appreciate the follow up. Thanks!

Justin

Replies
3
Views
199
Replies
4
Views
107
Replies
1
Views
108
Replies
3
Views
330
Replies
1
Views
226

1,181,092
Messages
5,928,038
Members
436,583
Latest member
AlxBtx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back