How to convert text to date

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,
I hope someone can assist with this problem.
My spreadsheet has a column of dates that import as text, with a column of acct #, and a column of $ amounts.
I need to subtotal the sheet by acct # and month/year.
My first problem is that the date '01/02/2009' looks like this '90102'. I need to get it formatted correctly in order to subtotal.
When I try to format as a date, it changes to '9/8/2146'. I have tried special, custom, function, etc.

Your help is greatly appreciated!

sh

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
I do not know how the data is entered.
select a date cell and see the formula bar.
if it is m/d/yyyy
then the date is entered correctly. You can format in any form using format numbmer -date.

If the formula bar also shows 90102 then you have to use a formula
I hope this dates is 2nd January 2009 then in that case in another empty cell copy this formula

=(MID(A1,2,2)&"/"&RIGHT(A1,2)&"/0"&LEFT(A1,1))+0

now you format the new entry as any DATE format.

if you carefully study the formula you can do similar formulas in the future

when you use mid, left or right it becomes a text . to convert this into number you use that zero (0) at the end .
1