Date Mask for use in MS Excel

[Duncan Findlay]

Hi:

In MS Access there are masks available to restrict input info. Are there
such masks available in MS Excel? I specifically am looking for something
similar to MS Access' InputMasks fo use in MS Excel. My purpose is to
restrict the nature of input into a column reserved for dates only so that
the dates can be used as an acceptable "integer" to make comparisons with
dates in other cells.

Any suggestions?

Duncan

 

[John Nurick]

Hi Duncan,

You're more likely to get an authorative answer if you ask in an Excel
group. But as far as I know Excel doesn't have input masks. However you
could probably use a function such as ISDATE() in the data validation
for the cells in question.

 

[Duncan Findlay]

Thanks, John:

I have MS Excel 2000 and ISDATE() does not appear in my list of functions.
Is it possible to provide me with the parameters of this function so I can
try it?

Thanks,

Duncan

 

[John Nurick]

Hi Duncan,

As I said, this isn't the place for authoritative Excel answers, in fact
I couldn't even spell "authoritative". And there isn't an ISDATE()
function.

One approach would be to take advantage of the fact that Excel and
Access both store date/times as numbers (e.g. the first moment of today
was 38222 and 6 pm was 3822.375. So you can check that a cell contains a
number that's an appropriate date by using a formula like
=AND(B3>38000,B3<39000)

It's also possible to create an IsDate() worksheet function in VBA. This
seems to do the job but needs testing on awkward cases:

Public Function IsDate(V As Variant) As Boolean
IsDate = VarType(V) = vbDate
End Function

Just paste the code into a module in your workbook and then use it like
any other function
=IsDate(B3)

Thanks, John:

I have MS Excel 2000 and ISDATE() does not appear in my list of functions.
Is it possible to provide me with the parameters of this function so I can
try it?

Thanks,

Duncan

 

[Duncan Findlay]

Thanks, John:

I did go on the Excel newsgroup and found that using data validation will
help to solve my problem (Data=>Validation) - not exactly, but probably good
enough.

Thanks,

Duncan

Hi Duncan,

As I said, this isn't the place for authoritative Excel answers, in fact
I couldn't even spell "authoritative". And there isn't an ISDATE()
function.

One approach would be to take advantage of the fact that Excel and
Access both store date/times as numbers (e.g. the first moment of today
was 38222 and 6 pm was 3822.375. So you can check that a cell contains a
number that's an appropriate date by using a formula like
=AND(B3>38000,B3<39000)

It's also possible to create an IsDate() worksheet function in VBA. This
seems to do the job but needs testing on awkward cases:

Public Function IsDate(V As Variant) As Boolean
IsDate = VarType(V) = vbDate
End Function

Just paste the code into a module in your workbook and then use it like
any other function
=IsDate(B3)

 

[Carol]

Duncan, if it is today's date you want, use =today() and
it automatically formats that for you and it changes as
the date does when you open it. If it is formatting
anykind you want, try Format-Cells-Nuimber and there
are a lot of types and many within each type. You can
format a celll, row or column that way.