Check if a cell contains a date

areesh

New Member
Joined
Dec 17, 2009
Messages
1
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))
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Upvote 0
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))
 
Last edited:
Upvote 0
The formula above should read:
Code:
=IF(LEFT(CELL("format",A1),1)="D",AND(A1>BOT,A1<EOFY))

Sorry. My first time at posting.
 
Upvote 0
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.
 
Upvote 0
The formula above should read:
Code:
=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))[>
 
Upvote 0
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 :)
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top