Convert numbers to date 20220801 -> 01082022

Ipsy

New Member
Joined
Feb 4, 2025
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have had a look through the forum but wasn't able to find an answer that worked for me, possible I didn't understand it either, I did end up with a lot of hashtags in my cell !

I am looking for a nice and easy way to convert a column of numbers from one format to another :

For example, I have 20220801, which is the way the date is presented in my file, but I need it to read 01082022 or 010822

Any help would be greatly appreciated.
 
Try

Libro1
AB
1
22022080101/08/2022
Hoja1
Cell Formulas
RangeFormula
B2B2=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))



Use cell format to view:
Libro1
AB
1
22022080101082022
Hoja1
Cell Formulas
RangeFormula
B2B2=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
 
Upvote 0
Thank you for your reply.

I copied and pasted your formula and received the particularly unhelpful and dreaded message below :
Both columns A and B are formatted "Standard" is there something else I should be doing?
1738687297754.png
 
Upvote 0
Welcome to the Board!

Does your version of Excel perhaps using semi-colons instead of periods as argument separators?
If so, you will need to replace them in the formula DanteAmor gave you, i.e.
Excel Formula:
=DATE(LEFT(A2;4),MID(A2;5;2),RIGHT(A2;2))
 
Upvote 0
Hello there,

I tried your formula with the semi-colons and whilst I didn't get the dreaded message the cell just takes it as text.



1738687934032.png
 
Upvote 0
Make sure the cell is not formatted as Text before you enter the formula. Also, Joe missed a couple of commas:

Excel Formula:
=DATE(LEFT(A2;4);MID(A2;5;2);RIGHT(A2;2))
 
Upvote 0
That means that the cell you placed it in is pre-formatted as Text!
Change the cell format to your desired date format, and re-enter your formula, noting Rory's edits.

(Thanks for the catch, Rory!)
 
Upvote 0
Try with semicolon:

Excel Formula:
=DATE(LEFT(A2;4);MID(A2;5;2);RIGHT(A2;2))
Change the cell format to custom use ddmmyyyy

1738688638992.png
 
Upvote 0
It looks as though you are using a French version of Excel, so try
Excel Formula:
=DATE(GAUCHE(A2;4);STXT(A2;5;2);DROITE(A2;2))
 
Upvote 0
N.B. My system's Regional Settings include short date dd-mmm-yy

T202502a.xlsm
ABCD
1Data Formatdd-mmm-yyGeneralyyyymmdd
2
3202208011-Aug-224477420220801
4202208011-Aug-224477420220801
5
1e
Cell Formulas
RangeFormula
B3B3=--TEXT(A3,"0000-00-00")
C3C3=--TEXT(A3,"0000-00-00")
D3D3=--TEXT(A3,"0000-00-00")
B4B4=--TEXT(LEFT(A4,8),"0000-00-00")
C4C4=--TEXT(LEFT(A4,8),"0000-00-00")
D4D4=--TEXT(LEFT(A4,8),"0000-00-00")
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,919
Members
453,767
Latest member
922aloose

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
Back
Top