SOLVED

Is_Date Help!

%3CLINGO-SUB%20id%3D%22lingo-sub-1642384%22%20slang%3D%22en-US%22%3EIs_Date%20Help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1642384%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20an%20excel%20cell%20to%20properly%20detect%20there%20is%20a%20date%20in%20the%20cell.%20And%20then%20return%20the%20date%20to%20the%20cell.%20I%20cannot%20get%20this%20reliably%20working.%20Sometimes%20it%20works%2C%20but%20if%20I%20start%20with%20a%20text%20field%20in%20the%20cell%2C%20and%20then%20change%20it%20to%20a%20date%2C%20it%20returns%20the%20date%20as%20a%205%20digit%20text%20field%2C%20probably%20representing%20the%20date%20as%20a%205%20digit%20text%20field.%3C%2FP%3E%3CP%3EThis%20is%20the%20code%3A%3CBR%20%2F%3EFunction%20IS_DATE(rng%20As%20Range)%20As%20Boolean%3CBR%20%2F%3EIS_DATE%20%3D%20IsDate(rng)%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3CP%3EThen%20in%20a%20cell%2C%20I%20pass%20in%3A%3CBR%20%2F%3E%3DIF(IS_DATE(C3)%2CC3%2CTRIM(C3))%3C%2FP%3E%3CP%3ESo%20if%20it's%20a%20date%2C%20I%20just%20want%20the%20date.%20If%20it's%20text%2C%20I%20want%20it%20to%20TRIM%20the%20text.%3C%2FP%3E%3CP%3ESo%20when%20stepping%20through%20the%20code%2C%20it%20ALWAYS%20properly%20detects%20if%20a%20date%2C%20but%20then%20upon%20return%2C%20the%20date%20might%20show%20properly%20as%2004%2F04%2F1970%2C%20but%20it%20sometimes%20will%20show%20a%205%20digit%20number%2C%20such%20as%2012345.%3C%2FP%3E%3CP%3EIs%20there%20a%20simple%20way%20to%20do%20this%2C%20or%20am%20I%20looking%20at%20a%20fairly%20complex%20process%20here...%3C%2FP%3E%3CP%3EPass%20a%20cell%20into%20the%20vba%20code.%20Grab%20the%20formatting%20of%20the%20cell.%20If%20the%20value%20is%20a%20date%2C%20not%20only%20send%20the%20date%20back%20from%20the%20code%2C%20but%20send%20the%20formatting%20of%20the%20cell%2C%20and%20then%20set%20that%20formatting%20into%20the%20cell%20also%3F%3C%2FP%3E%3CP%3EI%20will%20explain%20further%20if%20necessary%2C%20but%20I%20think%20somebody%20who%20has%20been%20down%20this%20path%20probably%20knows%20how%20subtle%20this%20is.%3C%2FP%3E%3CP%3EHelp!%3F!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1642384%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1647066%22%20slang%3D%22en-US%22%3ERe%3A%20Is_Date%20Help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1647066%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F582167%22%20target%3D%22_blank%22%3E%40DerekHart%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20to%20do%20the%20If%20in%20VBA%2C%20then%20forcing%20the%20formatting%20you%20need%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EFunction%20IF_IS_DATE(rng%20As%20Range)%20As%20String%0AIf%20IsDate(rng)%20%3D%20True%20Then%0AIF_IS_DATE%20%3D%20Format(rng%2C%20%22Long%20Date%22)%0AElse%0AIF_IS_DATE%20%3D%20Application.WorksheetFunction.Trim(rng)%0AEnd%20If%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1650047%22%20slang%3D%22en-US%22%3ERe%3A%20Is_Date%20Help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1650047%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746148%22%20target%3D%22_blank%22%3E%40Ramiz_Assaf%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BThis%20gives%20me%20%23Name!%20in%20the%20excel%20field.%20Can%20you%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted
Best Response confirmed by DerekHart (New 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

 

Highlighted

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

Highlighted

@DerekHart 

 

please find the file with the macro

it works perfectly

What Excel version are you using?

 

 

Highlighted

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