Date String To Serial

[goss]

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

 

[Satti Charvak]

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.

 

[Gary''s Student]

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))

 

[Fred Smith]

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.

 

[Ron Rosenfeld]

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

 

[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

 

[Ron Rosenfeld]

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

 

[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

 

[Ron Rosenfeld]

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

 

[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

Thanks Ron,

My extract code had a handler so if the date was not found a zero was
returned
Once the date is found, fill down to all records
So rows 1 and 2 had zeroes, all other rows had the date string
I added a test for If Not IsNumeric(C.Value) and now the code works
great!

Thanks!
goss

Snippet:
'Replace
For Each C In myRange
If Not IsNumeric(C.Value) Then
s = Replace(C.Value, "'", ", 20")
C.Value = DateValue(s) + TimeValue(s)
C.NumberFormat = "MM/DD/YYYY"
End If
Next C

 

[Ron Rosenfeld]

Thanks Ron,

My extract code had a handler so if the date was not found a zero was
returned
Once the date is found, fill down to all records
So rows 1 and 2 had zeroes, all other rows had the date string
I added a test for If Not IsNumeric(C.Value) and now the code works
great!

Thanks!
goss

Snippet:
'Replace
For Each C In myRange
If Not IsNumeric(C.Value) Then
s = Replace(C.Value, "'", ", 20")
C.Value = DateValue(s) + TimeValue(s)
C.NumberFormat = "MM/DD/YYYY"
End If
Next C

Glad to see you got it working.

Although if you have a zero in a cell, IsNumeric should test TRUE.

Another approach would be to add an error handler, with an appropriate message
depending on the error.
--ron

 

[goss]

Glad to see you got it working.

Although if you have a zero in a cell, IsNumeric should test TRUE.

Another approach would be to add an error handler, with an appropriate message
depending on the error.
--ron- Hide quoted text -

- Show quoted text -

Thanks Ron,

The test does return TRUE for the first 2 rows, so the zeroes are not
formatted and no type mismatch error is received
The code then executes as expected for all remaining rows

Thanks!
goss