Link to home
Start Free TrialLog in
Avatar of AnandSahoo
AnandSahoo

asked on

Macro in Excel to convert string to Date value in Microsoft Excel.

Dear Team,

Need some help in Excel (Any version 2003 or 2007)

I have 2 date columns in a huge excel file generated automatically by a job.

One column store date like 2011/05/02 10:25:30 AM EDT
another column store date like 2011/05/02 10:25:30 AM.

Can someone help me
with excel macro to delete the EDT stuff from 1st column and convert that column from string to Date format.
and convert the 2nd column from string to Date format.

This will help me to do the necessary date calculation & Pivot Etc i Excel file.

Thanks


Avatar of rspahitz
rspahitz
Flag of United States of America image

You can add a formula like this:

=DATEVALUE(LEFT(B1,22))

This will take the first 22 characters and, if in the valid format you indicated, convert it into an internal Excel date (which may need to be formatted as a date to see it properly.)
To be a bit more specific, you can try this formula, which will search for the EDT, if it exists, and remove it before converting:

=DATEVALUE(   TRIM(REPLACE(B1,FIND(" EDT",B1& " EDT"),4,""))   )
Avatar of Saqib Husain
You can also do a search/replace for the EDT which will directly give you the date as a date

For the other dates entered as text you can
- select a blank cell
- copy it (ctrl-c or any other preferred method)
- select your data and paste-special with option "Add"
SOLUTION
Avatar of Eric Zwiekhorst
Eric Zwiekhorst
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi AnandSahoo whatever your name is

I have attached a demo file to try.
Rub the sub called ConvertColumns.

Best regards

Rowan



Demo.xlsm
Avatar of AnandSahoo
AnandSahoo

ASKER

Thanks Everyone for the quick responses.
Since I am looking for a macro which for this task (Not a formula) I want to go with Rowan's comment. I have tested it and its able to solve the issue upto some extent.

Hi Rowan,

This is Anand.

I have tried using the macro. Looks like its changing the column A properly but column B is still "General" format and I am not able to do a difference on this.

Am attaching the demo data, Can you please help me with this. In Actual file The columns are V and DJ.
Demo-Data.xls
Hi Anad,

My solution will take care of everything and is mouch smaller.. Please try this also..
Hi Anand

Try this one.
It uses the / and the : to identify the date and time parts of the string
it removes the preceding text qualifiers (single inverted comma) if present
it will put the seconds at zero if they are not specified

Input string must be month/day/year but output date will be whatever format your system is set on.

It does not rely on the length of the input string to work.

Best regards Rowan

Demo.xlsm
Thanks Rowan,Eric,

Now both the Macro's works upto some extent.

This huge excel file date columns, I want to map with Access or some other reporting tools to produce some report automatically on daily basis.

After running the Macro,

I can connect to column 'A' without any problem and it recognises as date format.
Column A Format is - Custom - m/d/yy h:mm AM/PM

But Unable to connect to column 'B'
Column B format is -Custom - m/d/yyyy h:mm

When I chnage the column B format to A format manually by selecting format cell, I am able to connect column B as well.

Hence Is there any way to make both the columns to same format as A along with the exsting macro.

Thanks
Dear Anand,

My macro should do just that.
Please try it again,

ps just one or 10000 speed is the same..

kind regards

Eric
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial