Convert Text to date

[Guest]

How to convert a text to date in Excel 2002
e.g:
---------------------------------------------------------------------------------------
Started Finished Total Run Time
---------------------------------------------------------------------------------------
May 14, 2004 10:00:00 AM May 14, 2004 10:48:07 AM
---------------------------------------------------------------------------------------

How to get the total run time

I used
C2 = TEXT(B2-A2, "d h:mm:ss"
This can be done but only if
A2 = 5/14/2004 10:00:00 AM
B2 5/14/2004 10:48:07 AM

 

[Frank Kabel]

Hi
you have to make sure that your dates are stored as real
Excel date values. How do you enter these dates (or are
these values imported)?

 

[Guest]

This value is imported from third party software

 

[Frank Kabel]

Hi
after importing the data try to use
'Data - Text to columumns' and step to this wizard to
convert these values to real Excel date/time values

 

[Guest]

I have done the convertion but it still consider it as text.

 

[JMay]

=DATEVALUE(B4) will convert to Excel Value;
then format cell(s) as Date
HTH

 

[Frank Kabel]

Hi
also check the format of these cells prior to using this procedure (set
it to a date format). And also make sure there're no other blank/hidden
characters in your data

 

[Guest]

Hi
Thanks for the ideas. I have found the solution as below
Macro to calculate Total Run time between 2 text dat

Step 1:Change text to date format

Sub TestData1(
' Loop to cycle through each cell in the specified range
'For Each x In Range("A2:A30"
For Each x In Range("B2:B30"
x.Value = FormatDateTime(x.Value, vbGeneralDate
Nex
End Su

Step 2:Calculation in the cell or can put it in the Macr

= Text(B2-A2,"d h:mm:ss")

 

[Guest]

I can't get that to work in Excel 2003 under Win XP Home. It returns the error #value!.
I can't get DATEVALUE ("8/22/2008") to work, either, and that's an example in the Help! It returns the same error #VALUE!.

 

[Dave Peterson]

Sounds like USA date settings were used for the help:

What do you get if you put:
=datevalue("22/8/2008")
in a cell (and format it with a date format)

(or you could change your PC's date settings to match us in the USA!!!)

close excel
windows start button|settings|control panel|regional settings applet
Date Tab (fix that short date setting)
(I use win98, so your steps might be slightly different.)

(and change it back when you're done)

====
And this worked for me:
=DATEVALUE("May 12, 1997")
but this returned an error
=DATEVALUE("May 12,1997")
(no space before the year)

So =datevalue() can be picky.