Enter date into Formula

[DRA]

I want to have a date without using date function in another function, i.e.,
MATCH(01/02/2010,OFFSET(CC1stDate,1,0):OFFSET(CCDateCol,CCRowLast-1,0),1)+5

I use the #01/02/2010# in VBA. Is there a simular notation in EXCEL?

 

[Ron Rosenfeld]

I want to have a date without using date function in another function, i.e.,
MATCH(01/02/2010,OFFSET(CC1stDate,1,0):OFFSET(CCDateCol,CCRowLast-1,0),1)+5

I use the #01/02/2010# in VBA. Is there a simular notation in EXCEL?

The DATE function would be preferred as it is unambiguous and independent of
regional settings.

You could store the DATE in some cell, and reference that cell in your formula
-- That is probably the most flexible method.

If you must enter a date without using the date function, you could use, for
example: --("01/02/2010"). How that date gets interpreted will depend on your
Windows Regional/Language settings. On my system it will be interpreted as
2-Jan-2010 whereas on another system (or even on mine if I should happen to
change my Windows regional settings), it could get interpreted as 1-Feb-2010

DATE(2010,1,2) can ONLY be interpreted as 2-Jan-2010 regardless of the Windows
regional settings.
--ron

 

[Ron Rosenfeld]

I want to have a date without using date function in another function, i.e.,
MATCH(01/02/2010,OFFSET(CC1stDate,1,0):OFFSET(CCDateCol,CCRowLast-1,0),1)+5

I use the #01/02/2010# in VBA. Is there a simular notation in EXCEL?

Oh, one other way of entering the DATE into the function in an unambiguous
method, so long as ENGLISH is your language, would be:

--("10-Jan-2010")

However, this will not work with foreign language settings that have different
words for the months of the year.
--ron

 

[DRA]

I used :
=MATCH("01/02/2010",OFFSET(CC1stDate,1,0):OFFSET(CCDateCol,CCRowLast-1,0),0)

and

=MATCH(("01/02/2010"),OFFSET(CC1stDate,1,0):OFFSET(CCDateCol,CCRowLast-1,0),0)

Neither worked.

Help.