CODEWITHSUNDEEP
excelexcel-formula
user898082
user898082

Reputation:

Excel: Check cell for date

Short story short:

I want to check the cell C21 if it contains a date. I can't use VB cause it's deactivated by GPO.

Used this from this page

D21 contains this:

=WENN(ISTZAHL(DATWERT(C21));"date";"no date")
in english
=IF(ISNUMBER(DATEVALUE(C21))...

C21 this:

=HEUTE() # in english: =TODAY() Maybe other dates later, but allways in the correct format

but it allways returns "no date"

Upvotes: 13

Views: 127118

Answers (9)

cyberponk
cyberponk

Reputation: 1766

I tried every solution posted but none of them work if there are empty cells in the date cells, so I added a check to GBGOLC's answer and now it works with empty cells:

=IF(NOT(OR(ISBLANK(C21),ISERR(DATE(DAY(C21),MONTH(C21),YEAR(C21))))),"Yes it is a date","Not a date")

Upvotes: 0

Jozef
Jozef

Reputation: 9

try this formula (depends on date format):

=IF(DATE(TEXT(C21;"yyyy");TEXT(C21;"mm");TEXT(C21;"dd"));"contains date";"doesn't")

Upvotes: 0

Mahhdy
Mahhdy

Reputation: 592

Some of provided answers, checks cell format, which will not considers cell value, as you can format differently each cell no matter of its content, for checking if a cell value is date you can use this: 

if(ISERROR(VALUE(c21)),"No Date", ----do staff for Date ----)

or shorter version which just will inform you "no date" status. If a date entry is find, it will return its numerical value instead:

IFERROR(VALUE(c21),"No Date")

hope this helps,
Cheers,
M

Upvotes: 4

daniel
daniel

Reputation: 1

Just wanted to add to the discussion that although you can check with a date(year;month;day) it will give you false positives, since the way it works is that it seems to just roll over values that exceed the logical limit, i.e. if you have the date 99-12-35 it will assume that you meant to write Jan 4th 2000, since that is the 35 - the 31 days of December. In other words, you will find some errors, but not all. Same goes for months exceeding 12.

Upvotes: 0

Giri
Giri

Reputation: 11

If the value is a string this method would work.

TEXT(DATE(VALUE(RIGHT(AI8,4)),VALUE(MID(TRIM(AI8),4,2)),VALUE(LEFT(AI8,2))),"dd.mm.yyyy") = AI8

Upvotes: 0

GBGOLC
GBGOLC

Reputation: 600

Use this formula, the expression will return TRUE if cell A1 contains an invalid date.

=ISERROR(DATE(DAY(A1),MONTH(A1),YEAR(A1)))

This formula works by evaluating each component part of the date: DAY, MONTH and YEAR and then aggregating them using the DATE function.

ISERROR will the catch any errors by returning TRUE (invalid) otherwise FALSE (valid).

Obviously the date value in cell (A1) must contain values >= 01/01/1900.

Useful for "Conditional Formatting".

Upvotes: 11

Máté Juhász
Máté Juhász

Reputation: 2327

Use this: =IF(LEFT(CELL("format",C21))="D",..,..). Learn more about CELL formula here.

In your example =TODAY() already a real date and not a date stored as text, so doesn't make too much sense to use DATEVALUE there.

Update

Here are some example dates and how CELL recognize them: 

format          value           output
dd/mmmm/yyyy    12/June/2015    D1
dd/mm/yyyy      12/06/2015      D1
yyyy            2015            G
general         2015            G
dd/mm           12/06           D2
mmmm            June            G
dd mmmm yyyy    12 June 2015    G

Note: CELL is not volatile, so if the format of source cell is changed it won't be refreshed automatically, you need to either recalculate your sheet / book, either open the formula and press enter (also automatice recalculation initiated by any other volatile formula will cause it to refresh).

Upvotes: 14

Imran Khan
Imran Khan

Reputation: 17

use the following formula ...

=IF(NOT(ISERROR(DATEVALUE(TEXT(C21,"mm/dd/yyyy")))),"valid date","invalid date")

I think it will solve your problem.

Upvotes: 0

teylyn
teylyn

Reputation: 35915

Excel stores dates as numbers. 1 is 1-Jan-1900.

When it comes to numbers in a cell, Excel cannot tell if a number is meant to be a number or a date.

Today is 11/06/2015 as a date and 42166 as a number. For the consumer of a spreadsheet, the cell can be formatted to display the number as any number format or as a date. Excel formulas cannot tell whether the number in that cell is "meant" to be a date.

So, there is no Excel formula that you can use in a spreadsheet that will tell you if cell A1 is about 42166 widgets or if contains the date of June-11-2015.

Upvotes: 8

Related Questions