Excel 2003 - VBA? - Text to date info conversion

  • Thread starter Thread starter Craig Brandt
  • Start date Start date
C

Craig Brandt

Hi guys:

I am pulling data from a db where the time comes to me in text ( 12/9/2008
11:41 AM EST ) . How do I force this into the excel format for date and
time?

Best regards,
Craig
 
You just have to get rid of the EST part, using the RIGHT() function. Then
multiply by 1 and format as Date/Time.
If the number of spaces varies, it gets more tricky. If that is the case,
post again in this same thread.
 
Niek:

The following formula, using your suggestion, solved my problem.

Cell A1: 12/10/2008 5:46 AM EST
Cell B1: =SUBSTITUTE(A1,"EST","")*1 or =LEFT(A1,FIND("EST",A1)-1)*1

Then format B1 as a date. Both work like a champ.

Thank you much,

Craig
 
Select all the cells with this data in it, then click Data/Text To Columns
from Excel's menu bar. On the Step 1 panel, pick the "Fixed Width" option,
then click Next. On the Step 2 panel, remove all but the last vertical line
by double clicking on them (that is, leave only the vertical line separating
the "space plus EST" at the end, then click Next.. On Step 3 panel, select
the first column in the table and click the Date option button (select the
appropriate date ordering from the drop down... MDY is my guess), then click
the second column in the table and select the "Do not import column (skip)"
option button for it. Finally, click the Finish button.
 
Rick:

Thanks for the suggestion. I went through the steps so that I would
understand it in the future, but for this example it has one shortcoming:,
and that is that the data length changes. 1/1/2008 1:12 AM EST has 20
characters and 12/30/2008 10:45 AM EST has 23 characters. I am also
automating the effort and the formula path is so much easier for me to do.

Thanks again

Craig
 
Just to follow up... you can still salvage the manual method I posted as it
turns out. On Step 1, select "Delimited"; on Step 2, uncheck Space if it is
checked and put a check in the check box with caption "Other", then put M in
the blank field next to it; in Step 3, do the same operations on the two
columns that I previously gave you; then click Finish.
 
Back
Top