Hi all,

Is there a way to convert this date string

OCT 23'08 6:04PM

into serial that Excel will recognize as a number instead of a string?

Thanks!
goss

In Excel, if only the dates are netred then the dates are actually stored as
numbers (integers) and when you add time to date then that numbers become
decimal numbers.

Actually, if you change the format of the cell to numbers without decimals
you will get the result.

--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India


"goss" wrote:

Hi all,

Is there a way to convert this date string

OCT 23'08 6:04PM

into serial that Excel will recognize as a number instead of a string?

Thanks!
goss

Assuming that the data is in Text format, in a work area, say E1 tru F12
enter this table:

JAN 1
FEB 2
MAR 3
APR 4
MAY 5
JUN 6
JUL 7
AUG 8
SEP 9
OCT 10
NOV 11
DEC 12

Then with your value in A1:

=DATE(2000+MID(A1,8,2),VLOOKUP(LEFT(A1,3),E1:F12,2 ,FALSE),MID(A1,5,2))

--
Gary''s Student - gsnu200812


"goss" wrote:

Hi all,

Is there a way to convert this date string

OCT 23'08 6:04PM

into serial that Excel will recognize as a number instead of a string?

Thanks!
goss

You can use Text to Columns.
Specify a fixed width for two columns, one for the date, and one for the
time.

You can discard the time, if you want, by skipping the column.

Regards,
Fred.

"goss" wrote in message
...
Hi all,

Is there a way to convert this date string

OCT 23'08 6:04PM

into serial that Excel will recognize as a number instead of a string?

Thanks!
goss

On Fri, 7 Nov 2008 03:18:42 -0800 (PST), goss wrote:

Hi all,

Is there a way to convert this date string

OCT 23'08 6:04PM

into serial that Excel will recognize as a number instead of a string?

Thanks!
goss

If all of your dates will be in this century, then:

and if the format will always be:

mmm dd\'yy h:mmAM/PM

=--REPLACE(REPLACE(A1,LEN(A1)-1,," "),7,1,", 20")

If the format of the string may be different, then post back with more details.
--ron

On Nov 7, 5:43*am, Ron Rosenfeld wrote:
On Fri, 7 Nov 2008 03:18:42 -0800 (PST), goss wrote:
Hi all,

Is there a way to convert this date string

OCT 23'08 6:04PM

into serial that Excel will recognize as a number instead of a string?

Thanks!
goss

If all of your dates will be in this century, then:

and if the format will always be:

mmm dd\'yy h:mmAM/PM

=--REPLACE(REPLACE(A1,LEN(A1)-1,," "),7,1,", 20")

If the format of the string may be different, then post back with more details.
--ron

Thanks all!

Ron,

Works great!
Yes, The String is extracted from a UNIX report and will always be
same format
But instead of adding a helper column can I use For Next Loop to go
through the range and use approximately the same function?
I tried but received

"Compile error:
Argument not optional"

Here is my code snippet

'Replace
For Each C In myRange
C.Value = C.Replace(Replace(C.Value, Len(C.Value) - 1, , " "),
7, 1, ", 20")
Next C

Thanks!
goss

On Fri, 7 Nov 2008 06:34:19 -0800 (PST), goss wrote:

On Nov 7, 5:43*am, Ron Rosenfeld wrote:
On Fri, 7 Nov 2008 03:18:42 -0800 (PST), goss wrote:
Hi all,

Is there a way to convert this date string

OCT 23'08 6:04PM

into serial that Excel will recognize as a number instead of a string?

Thanks!
goss

If all of your dates will be in this century, then:

and if the format will always be:

mmm dd\'yy h:mmAM/PM

=--REPLACE(REPLACE(A1,LEN(A1)-1,," "),7,1,", 20")

If the format of the string may be different, then post back with more details.
--ron

Thanks all!

Ron,

Works great!
Yes, The String is extracted from a UNIX report and will always be
same format
But instead of adding a helper column can I use For Next Loop to go
through the range and use approximately the same function?
I tried but received

"Compile error:
Argument not optional"

Here is my code snippet

'Replace
For Each C In myRange
C.Value = C.Replace(Replace(C.Value, Len(C.Value) - 1, , " "),
7, 1, ", 20")
Next C

Thanks!
goss

Yes you can, but VBA is a bit "smarter", so you could use something like:

For Each c In MyRange
s = Replace(c.Value, "'", ", 20")
c.Value = DateValue(s) + TimeValue(s)
c.NumberFormat = "mmm dd, yyyy h:mm am/pm"
Next c
--ron

On Nov 7, 11:52*am, Ron Rosenfeld wrote:
On Fri, 7 Nov 2008 06:34:19 -0800 (PST), goss wrote:
On Nov 7, 5:43*am, Ron Rosenfeld wrote:
On Fri, 7 Nov 2008 03:18:42 -0800 (PST), goss wrote:
Hi all,

Is there a way to convert this date string

OCT 23'08 6:04PM

into serial that Excel will recognize as a number instead of a string?

Thanks!
goss

If all of your dates will be in this century, then:

and if the format will always be:

mmm dd\'yy h:mmAM/PM

=--REPLACE(REPLACE(A1,LEN(A1)-1,," "),7,1,", 20")

If the format of the string may be different, then post back with more details.
--ron

Thanks all!

Ron,

Works great!
Yes, The String is extracted from a UNIX report and will always be
same format
But instead of adding a helper column can I use For Next Loop to go
through the range and use approximately the same function?
I tried but received

"Compile error:
Argument not optional"

Here is my code snippet

* *'Replace
* *For Each C In myRange
* * * *C.Value = C.Replace(Replace(C.Value, Len(C.Value) - 1, , " "),
7, 1, ", 20")
* *Next C

Thanks!
goss

Yes you can, but VBA is a bit "smarter", so you could use something like:

For Each c In MyRange
* * s = Replace(c.Value, "'", ", 20")
* * c.Value = DateValue(s) + TimeValue(s)
* * c.NumberFormat = "mmm dd, yyyy h:mm am/pm"
Next c
--ron- Hide quoted text -

- Show quoted text -

Thanks Ron,

Option Explicit is on

I was not sure of data type for s?
I tried S as string and long
Neither worked

I receive A Typ Mismatch Error Message

Thanks!
goss

On Fri, 7 Nov 2008 12:38:44 -0800 (PST), goss wrote:

Thanks Ron,

Option Explicit is on

I was not sure of data type for s?
I tried S as string and long
Neither worked

I receive A Typ Mismatch Error Message

Thanks!
goss

s is String.

As in:

======================
Option Explicit
Sub foo()
Dim s As String
Dim MyRange As Range, c As Range

Set MyRange = Range("a1") 'obviously should be changed.

For Each c In MyRange
s = Replace(c.Value, "'", ", 20")
c.Value = DateValue(s) + TimeValue(s)
c.NumberFormat = "mmm dd, yyyy h:mm am/pm"
Next c
End Sub
=======================
--ron