Change a number into a date

**Frick** · 03-17-2025, 02:13 PM

When I import a txt file and convert into columns I have 1 column that is a text number (Ex. 20250222). What i need is a formula to convert that text into a date such that it becomes one of the following: 2025.02.22; or 02/22/2025. Maybe, this should be a VBA that executes on importing the date or with a macro button. Looking forward to some help.

**MrShorty** · 03-17-2025, 02:27 PM

Will the text number always be 8 digits like you show here? In that case I expect something like =DATE(LEFT(textnumber,4),MID(textnumber,5,2),RIGHT(textnumber,2)) will return the desired date serial number, which you can then format in whatever date format you desire using number formatting.

If you prefer VBA, VBA has similar text and date functions that can do the same thing.

**Rick Rothstein** · 03-29-2025, 12:39 AM

Originally Posted by MrShorty

Will the text number always be 8 digits like you show here? In that case I expect something like =DATE(LEFT(textnumber,4),MID(textnumber,5,2),RIGHT(textnumber,2)) will return the desired date serial number, which you can then format in whatever date format you desire using number formatting.

This shorter formula would also work the same way and handle the entire range all at once...

=0+TEXT(A1:A2000,"0000-00-00")

Note: Your formula will also handle the entire range all at once if you change each textnumber reference to the datarange reference.

**Frick** · 03-17-2025, 04:51 PM

As I need to convert all records in Column E to the date format, VBA that would do the conversion as the data in converted from text file to columns might be best way going forward. Each import is over 2,000 records. Help is greatly appreciated.

**MrShorty** · 03-17-2025, 06:13 PM

Why do you think VBA would be best? I can enter that formula into a cell and copy/paste/fill down a lot faster than I can write a VBA procedure for that.

As I said, VBA has the same basic functions. I would expect a similar VBA statement inside of a loop would work just fine.

For i=1 to 2000
Selection.cells(i,3).value=DateSerial(Left(Selection.cells(i,1).value,4),Mid(Selection.Cells(i,1).Value,5,2),Right(Selection.Cells(i,1).Value,2))
Next i

It's up to you how you want to proceed. So far, I have not seen anything in your description of the problem that would suggest to me that VBA is a better programming language for this task than simple worksheet functions.