Problem with CDate

Question

Hi,

I'm using CDate to convert particular date formatted as string to excel Date type. I wrote a small UDF for this conversion, however when I run the function as a in-cell function, it returns a number and the calling cells default format is set to Number type. When I manually convert it to Date type, excel will display the correct date entry.

So, I need to know whether there is a way to set default format of the calling cell to Date type through my Date conversion macro. Otherwise, user has to manually change format of each of cell.

e.g

 Function Test()
   Test = CDate("2010/12/23")
 End Function

Thank You

Answer 1

The calling cell is exposed via Application.ThisCell however your going to have to manually format before/after the call as an Excel UDF cannot modify the physical characteristic of a cell.

Answer 2

Perhaps you can run something after the cells have been entered?

Dim c As range
For Each c In ActiveSheet.UsedRange.Cells
    ''Case sensitive
    If c.Formula = "=test()" Then
        c.NumberFormat = "d/mm/yyy"
    End If
Next

Answer 3

It sounds like what you want to do is actually a two-part process: convert a value in a cell (or range of cells) to a date and then display it as a date, as opposed to simply converting a text value that looks like a date to a date value.

If that is the case, then I would recommend modifying Remou's suggestion like so (using UsedRange can be problematic on worksheets containing large amounts of data):

Dim c As Range
For Each c In Selection.Cells
    c.Value = CDate(c.Value)
    c.NumberFormat = "m/d/yyyy"
Next c

The user would then need to select the cells to which the formatting should be applied and run the macro; it sounds as though you do not wish this to happen, but I'm not sure that is possible.

Depending on how you want to use the macro, you can add additional checks: for example, you could apply the formatting only to non-blank cells currently formatted as text (c.Value <> "" and c.NumberFormat = "@").