Date String To Serial

  • Thread starter Thread starter goss
  • Start date Start date
G

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
 
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.
 
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))
 
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.
 
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
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
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 -
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...


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
 
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
Click to expand...

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
 
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 -
Click to expand...

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
 
Back
Top