[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 662
  • Last Modified:

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


0
AnandSahoo
Asked:
AnandSahoo
  • 3
  • 3
  • 2
  • +2
2 Solutions
 
rspahitzCommented:
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.)
0
 
rspahitzCommented:
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,""))   )
0
 
Saqib Husain, SyedEngineerCommented:
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"
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Eric ZwiekhorstCommented:
In a macro it would look like this

Sub DTS()  

  Columns("A:A").Select 'column with EDT
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(23, 9)), TrailingMinusNumbers:=True

    Columns("B:B").Select 'column without EDT
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

End Sub

Kind regards

Eric
0
 
rowanscottCommented:
Hi AnandSahoo whatever your name is

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

Best regards

Rowan



Demo.xlsm
0
 
AnandSahooAuthor Commented:
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
0
 
Eric ZwiekhorstCommented:
Hi Anad,

My solution will take care of everything and is mouch smaller.. Please try this also..
0
 
rowanscottCommented:
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
0
 
AnandSahooAuthor Commented:
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
0
 
Eric ZwiekhorstCommented:
Dear Anand,

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

ps just one or 10000 speed is the same..

kind regards

Eric
0
 
rowanscottCommented:
Hi Anand

Here it is again. it formats the cell with m/d/yy h:mm AM/PM
Just added a line in the code.
It is slower than Erics method but it does allow for variations such as
"03/15/99 01:18:49 PM EDT"

Best regards
Demo.xlsm
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now