excel formula  
Author Message
arkiboys





PostPosted: Visual Basic for Applications (VBA), excel formula Top

hi,
In the formula section, how do you check if the value of a cell is a date
Thanks


Microsoft ISV Community Center Forums2  
 
 
ADG





PostPosted: Visual Basic for Applications (VBA), excel formula Top

Hi

I am not aware of an Excel function that does this directly. Excel stores dates as numbers 1 is 01/01/1900 etc, with the time stored as decimals after the integer. This does allow you to test if the cell contains a number which is equivalent to the date range you are looking for eg the below checks if the value in A3 can be interpreted *** a date between 01/01/06 and 16/09/06.

=IF(AND(INT(A3)>=38718,INT(A3)<=38976),"Is my date range","Not my date range")


 
 
gidyeo





PostPosted: Visual Basic for Applications (VBA), excel formula Top

Try this (say A1 is the cell to be checked):

=IF(OR(CELL("format",A1)="D1",CELL("format",A1)="D2",CELL("format",A1)="D3",CELL("format",A1)="D4",CELL("format",A1)="D5"),"date", "not date")

I've tested this using a few date formats but apparently it doesn't work when the cell is set to 14 March 2001 format, cuz the CELL function identifies it as a general format. Not too sure how Excel handles each different date formats.

Maybe someone else may shed more light on this. :)