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

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

" wrote:

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

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

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

In article .com,
wrote:

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

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.



" wrote:


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

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.

" wrote:

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

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.

--
Regards,
Tom Ogilvy


" wrote:

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

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

On Error GoTo 0


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


" wrote:

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