MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Converting dates

  Asked By: George    Date: Oct 10    Category: MS Office    Views: 1920

Can't quite figure out how to get roud this issue, so I was wondering
if the masses could give me some pointers....

I have a named range that can contain a variable number of rows, each
each row contains a date, however, some of the cells have the date as
text (aligned to the left of the cell) and some are dates propper
(aligned to the right of the cell). I have no control over how the
file is formatted prior to its arrival, so have to find a way of
dealing with the inconsistency. Is there an easy way using VBA to
convert all the cells in the named range to dates propper?

I was hoping that something along the lines of
Application.WorksheetFunction.DateValue would work, but it doesn't
look as if that function is available. The best guess of
Range("DATEFIELD").value = CDate(Range("DATEFIELD").value) also
doesn't work, presumably because ends up as a circular reference.
Ideally, I'd prefer not to have to create additional columns in the
worksheet, but just perform some operation on then named range as a
whole. Any ideas?



5 Answers Found

Answer #1    Answered By: Aamanee Khan     Answered On: Oct 10

Is this file an .xls excel file? If not, and you bring it into excel
(or open it in excel) you can frequently control  how excel tries to
interpret the data. What kind of file is it?

Answer #2    Answered By: Michele Grant     Answered On: Oct 10

The problem isn't your CDate as such.


Range("DATEFIELD").value = CDate(Range("DATEFIELD").value)

isn't a circular  reference - it's just a reassignment to itself.

The problem is that CDate won't take your multi-cell range. You need to
loop through the cells. E.g.

Option Explicit

Private Sub CommandButton1_Click()
Dim cell  As Range
For Each Cell In Range("DateField")
Cell.Formula = CDate(Cell.Formula)
Next Cell
End Sub

This might change the format of your date  values. An alternative is to
check whether it's a string that's in the cell, and only convert if it is.

Private Sub CommandButton1_Click()
Dim Cell As Range
For Each Cell In Range("DateField")
If TypeName(Cell.Value) = "String" Then
Cell.Formula = CDate(Cell.Formula)
End If
Next Cell
End Sub

Answer #3    Answered By: Aberto Rossi     Answered On: Oct 10



selection.NumberFormat = "m/d/yy;@"

Answer #4    Answered By: Nina Garcia     Answered On: Oct 10

My suggestion is a bit left-field. Whilst I applaud your efforts to try
and account for the inconsistencies of file formats arriving, surely you
can exert some influence to ensure that files arrive in the correct
format (the format that you specify)? We have had similar issues at
work, and a bit of consultation, or stamping of feet, solved the

Alternatively, a proper date  is actually a numeric value of Julian date.
So, why don't you look if ISNUMERIC, and loop through the data that way,
correcting only non-numeric entries.

Answer #5    Answered By: Wilbert Patterson     Answered On: Oct 10

Thanks for all the replies, managed to get to a solution I was happy

Much as I'd like to hanve thrown the toys out of my pram and insist
they get the dates  consistent before sending the xls files to me it's
not possible. The inconsistency seems to arise in third-party software
that pivots SQL queries and generates xls file automatically. Far
easier to resolve the issue  as it crosses my desk - which has now been

Didn't find what you were looking for? Find more on Converting dates Or get search suggestion and latest updates.