Is_Date Help!

I want an excel cell to properly detect there is a date in the cell. And then return the date to the cell. I cannot get this reliably working. Sometimes it works, but if I start with a text field in the cell, and then change it to a date, it returns the date as a 5 digit text field, that dateserial thing that Excel uses in to properly store a date, but I don't want the cell changed to that.

This is the code:
Function IS_DATE(rng As Range) As Boolean
IS_DATE = IsDate(rng)
End Function

Then in a cell, I pass in:
=IF(IS_DATE(C3),C3,TRIM(C3))

So if it's a date, I just want the date. If it's text, I want it to TRIM the text.

So when stepping through the code, it ALWAYS properly detects if a date, but then upon return, the date might show properly as 04/04/1970, but it sometimes will show the 5 digit date number, such as 12345.

Is there a simple way to do this, or am I looking at a fairly complex process here...

Pass a cell into the vba code. Grab the formatting of the cell. If the value is a date, not only send the date back from the code, but send the formatting of the cell, and then set that formatting into the cell also?

I will explain further if necessary, but I think somebody who has been down this path probably knows how subtle this is.

Help!?!

Try to do the If in VBA, then forcing the formatting you need

Function IF_IS_DATE(rng As Range) As String
If IsDate(rng) = True Then
IF_IS_DATE = Format(rng, "Long Date")
Else
IF_IS_DATE = Application.WorksheetFunction.Trim(rng)
End If
End Function

Forum Discussion

Is_Date Help!

4 Replies

Resources