Check if a cell contains a date

[areesh]

Since there isn't a ISDATE function in excel.

The following can be used to simulate the same, and avoid having to achieve the same with VBA's IsDate function:

STEPS:
1- Format the column (ex A) as text

2- Formula to check for valid dates:
=ISERROR(DATEVALUE(A1))

 

[BGY23]

Hi areesh,

You can use the =Cell("format",cell address) formula to establish the format of the cell. Anything starting with a "D in the result is a proper date or time format.

If you type =cell( in a cell and use the help for the function it will give you a full list of the format types and the code it will return for the relative format.
For example dd/mm/yy results in "D1". Default format returns "G" (general)

Hope this is of use to you.

 

[etaf]

ignore - wrong reply

 

[Davo2079]

Hi areesh,

You can use the =Cell("format",cell address) formula to establish the format of the cell. Anything starting with a "D in the result is a proper date or time format.

If you type =cell( in a cell and use the help for the function it will give you a full list of the format types and the code it will return for the relative format.
For example dd/mm/yy results in "D1". Default format returns "G" (general)

Hope this is of use to you.

Thank you BGY23. This was a great help. I wanted to conditionally format all cells in my workbook that were for the previous financial year. Just checking for a cell value between two dates didn't work because values like $39,176 were also highlighted. Checking for a date field first eliminated this problem.

If anyone is interested in the future for something similar I used the formula below in conditional formatting:

=IF(LEFT(CELL("format",A1),1)="D",AND(A1>BOT,A1<EOFY))
<eofy))

BOT (beginning of time) is a defined name stored anywhere in the workbook (it doesn't have to be on the same worksheet) for the start date.
EOFY (end of financial year) is also a defined name for the day after eofy. I could have used the actual last day and changed my formula to A1<=EOFY.

One other tip:
You can only use conditional formatting on a sheet, not a book (as far as I am aware). So when I set up the first sheet I recorded a macro. Then I ran the macro against each sheet. Magic. Unfortunately I didn't think of that. I found it elsewhere on a forum.

If I had a lot of sheets to do I would have amended the macro to loop through all sheets automatically.

Hope this helps.</eofy))

 

[Davo2079]

The formula above should read:

=IF(LEFT(CELL("format",A1),1)="D",AND(A1>BOT,A1<EOFY))

Sorry. My first time at posting.

 

[MARK858]

Davo2079, you probably have a < in your formula, try putting a space either side of it as the forum is interpreting it as a HTML tag.

 

[Davo2079]

The formula above should read:

=IF(LEFT(CELL("format",A1),1)="D",AND(A1>BOT<eofy))[ code]

Sorry. My first time at posting.

PS.
Still didn't work even in CODE. When coding add <eofy)) with="" a="" less="" than="" symbol="" (opposite="" to=""><eofy)) after="" bot="" and="" replace="" the="" ^^="" with="" a="" less="" that="" sign="" (opposite="" to="">,A1^^EOFY)) after BOT and replace the ^^ with the less than sign (opposite to >).</eofy))></eofy))></eofy))[>

 

[MARK858]

See post No 6. By space I do mean literally use the spacebar

Example:
=IF(LEFT(CELL("format",A1),1)="D",AND(A1>BOT < 17)

or try one of the links in my signature on how to post a screenshot.

Edit: OP posted a reply as I was typing the previous edit. Leaving the post here in case anyone stumbles across it

 

[Davo2079]

Thanks MARK858. You're quite right. I thought I tried spaces, guess not properly or in the right places.

Again the formula:

=IF(LEFT(CELL("format",A1),1)="D",AND(A1>BOT,A1 < EOFY))

Remove spaces when coding.