Change Date format from a formula

jrv924

New Member
Joined
Jun 10, 2021
Messages
8
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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!

Thanks in advance!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,414
Office Version
  1. 365
Platform
  1. Windows
Like this?
Excel Formula:
=MID(A1,3,2) & "/" & RIGHT(A1,2) & "/20" & LEFT(A1,2)
 
Solution

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,460
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Here is another formula that you can try...
Excel Formula:
=TEXT(MID(A1&A1,3,6),"00\/00\/2\000")
 

jrv924

New Member
Joined
Jun 10, 2021
Messages
8
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,414
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 10, 2021
Messages
8
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,414
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 10, 2021
Messages
8
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
1,143,909
Messages
5,721,461
Members
422,363
Latest member
Bogus_Potatoes

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top