hi,
In the formula section, how do you check if the value of a cell is a date
Thanks
excel formula
Hot Topic
- subscript out of range
- Count and display in cell
- Possible to add multiple (300+) hyperlinks that run the same VBA code?
- hidden versus visible in pivot tables
- Excel: Pivot Tables: GETPIVOTDATA does not seem to work on custom fields.
- Backup and Restore Data/Metadata for a Novel Backend.
- Using parts of a cell value as a variable?
- Table or Array in the header of an excel sheet
- Using Proper function to Format Worksheet
- Left, Mid and Right functions not working on Office 2003
Microsoft ISV
- datagrid control in access
- Excel Macro to Capture Live Webcam Image?
- Loop til you drop
- Limitations of business scorecard manager
- Need help writting a script similar to...
- DLookup help?
- Access Doesn't Close after ActiveX is inserted
- iexplore.exe - Application Error
- I am searching a VBS scripting example for a free form path item in Adobe Illustrator
- Text wraping on edit box of dialogsheet
excel formula
ZergaKilla
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")
RizwanSharp
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. :)