Sep 07 2020 01:04 PM - edited Sep 07 2020 02:03 PM
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!?!
Sep 08 2020 10:56 PM
Solution
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
Sep 09 2020 11:10 AM
@Ramiz_Assaf This gives me #Name! in the excel field. Can you help?
Sep 09 2020 11:25 AM
Sep 09 2020 12:47 PM
@Ramiz_Assaf super appreciated! Apologies, user error on my part. Working great.
Sep 08 2020 10:56 PM
Solution
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