asked on

Excel: How to easily convert a date in Date format to General format

I am reloading a load of data files into a database and the dates in many of the older files are in Date *3/14/2001 format. The import is failing because the import program is expecting a General format. If I just change the date to a General format, it becomes a large integer number, which is not what I want... I need the same date but in General format.

Example:
Date in old Excel file=11/26/2003 Excel format:=Date *3/14/2001 Fx=11/23/2003 8:00 AM
Date in new Excel file=10/3/2006 Excel format=General Fx='10/3/2006 08:50:00

The latter date format is what I need. I have no control over the import program, so this must be strictly an Excel correction that can be made and then copied and pasted to correct all of the dates in each "old" file. Also, I'm a basic Excel person, so I don't know how to do anything fancy. I just need a basic way to convert a date in Date 3/14/2001 format to a General field so that the date doesn't show up as a big integer, but as the same date but with a General format.

I thought of just doing a formula that concatenates a single quote with the existing date, but I'm really not sure that that would be the solution and I don't have the time to keep trying things.

Thank you in advance for your help. I need an answer very quickly.

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bshelby777

ASKER

Thanks, Barry. I'll try that. Question: can I just do it in the same column? For example, if the date is in column A, can I just put the formula in column A referencing column A, or does that mess up the value in column A?

barry houdini

No you can't use a formula in the same column but you can create the "text dates" in a new column and then copy them back over the originals to get the same effect.
Once you have the new column just copy that and then select the original column and use
Edit > Paste Special > values

Dave

Alternatively you could use VBA to replace the contents insitu. The code below runs on a user selected range (inputbox prompt)> if you have many files that the code can be adpated to loop through all the files, open them , convert the contents, then save
Cheers
Dave

Sub ConvertInsitu()
    Dim rng1 As Range, X(), i As Long, j As Long
    Set rng1 = Application.InputBox("select range to convert", Default:=Selection.Address, Type:=8)
    X = rng1
    For i = 1 To UBound(X, 1)
        For j = 1 To UBound(X, 2)
            X(i, j) = "'" & Format(X(i, j), "m/d/yyyy")
        Next j
    Next i
    rng1 = X
End Sub

Open in new window

bshelby777

ASKER

Hi Barry! Forget my last question - I tried it and got a circular reference.

I'll just need to create a new column and do as you suggested. Then I need to import it and see how it came out. I'm on something else right now, so will do it in about an hour and get back to you.
Thank you so much for your help.

bshelby777

ASKER

Hi Barry,

Sorry, I didn't get your last message until I sent mine. I'll be in touch within an hour.

bshelby777

ASKER

Hi Barry and Dave,

Barry's solution worked perfectly for an Excel beginner:
1) Insert "dummy" column (for example, A) next to the date column to convert (for example, B)
2) In the dummy column, first cell (or 2nd cell down if there are column headers), enter:
=TEXT(A1,"m/d/yyyy")
3) Copy the formula down the rest of the column
4) Select the entire dummy column (except the heading if there is one), and copy
5) Paste entire column over the original wrong dates in the original (A) column, Right click to paste instead of CTL-V and select Paste Special --> Values to paste the values instead of the formula, or just use Edit > Paste Special > values

Dave - thank you so much for your response, but it's too sophisticated for me. IYour solution would be oerfect if I had the time to figure out everything, but I just need a simple Excel solution right now. But thank you for your input. Someday, I may be resorting to VBA for some of these solutions.

bshelby777

ASKER

Hi Barry and Dave,

Did you get your points assigned (Barry 450, Dave 50)? I'm not sure I'm doing this correctly.

barry houdini

Hello bshelby777,
No, you don't appear to have accepted an answer, perhaps this link will help.....
regards, barry

Dave

No probs :)
Thx for the assist (in advance)
Cheers
Dave