Is Cell in Date Format

[dhockin]

I have a userform that has a text label that reports the calendar date/
time in the format of the activecell. All works fine when the cell has
been formated to a date format but it gets reported back in various
odd ball forms when in General format, currency, %, fraction etc.

This occurs when the cell has not yet been formated to a date
It there a way I can used to to determine if the cell is in a date
format so I can change the label in these situations

The isdate function will tell if the cell is a date but I need to know
the cell format before data is entered.

TIA

 

[Joel]

Therre is no reason you can't format the cell before data is entered.

 

[dhockin]

Yes Joel I could, but my form allows the user to enter the date using
one of 4 pre set formats ( dates, time or date & time) or in the the
format of the existing cell (which presumes it is a date format
already). The label shows them what it would look like, so I need to
know if it is a date format for the label. They can change after if
they want

 

[JE McGimpsey]

Can you not ignore format if you create the label using

Format(ActiveCell.Value, "dd mmm yyyy hh:mm:ss")

(or whatever format string you want)...


You could check the NumberFormat property for a particular format, e.g.:

If ActiveCell.NumberFormat Like "*yy*" Then
'likely a date - or use *m/d*, *d/m*, etc
Else
'Do something else
End If

 

[Joel]

the important requirment is that it is in any date time format and not other
formats. The date format is just a number with zero equalling Jan 1, 1900
and the whole number 1 represents one day. The different time date formats
just displays this number differently.

 

[JLGWhiz]

If the user is making the input through a control on the UserForm, then you
should be able to use the value of the user input/selection to format as soon
as it has been initiated. Format(ControlValue, "d/m/yy") with ControlValue
being substituted with the appropriate code structure and the date format
being changed to what you require.

 

[Tom Ogilvy]

If you don't want to parse the numberformat property, you could put a number
in it and check the vartype:

Activecell.Value = 1
? vartype(activecell.Value) = vbDate
True

then clearcontents the cell.

 

[Joel]

You can run the text through the datevalue function to determine if it is a
valid date

On Error Resume Next
xyz = DateValue("89:98")
If Err.Number <> 0 Then

MsgBox ("Date entered is not a valid date")
End If