Date Mask for use in MS Excel

  • Thread starter Thread starter Duncan Findlay
  • Start date Start date
D

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
 
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.
 
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
 
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
Click to expand...
 
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

John Nurick said:
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)
Click to expand...
 
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.
 
Back
Top