SOLVED

Is_Date Help!

Copper Contributor

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!?!

 

4 Replies
best response confirmed by DerekHart (Copper Contributor)
Solution

@DerekHart 

 

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

 

@Ramiz_Assaf   This gives me #Name! in the excel field. Can you help?

@DerekHart 

 

please find the file with the macro

it works perfectly

What Excel version are you using?

 

 

@Ramiz_Assaf super appreciated! Apologies, user error on my part. Working great.

1 best response

Accepted Solutions
best response confirmed by DerekHart (Copper Contributor)
Solution

@DerekHart 

 

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

 

View solution in original post