is there a way for excel to confirm if a value is a date or not?

Olargee

New Member
Joined
May 31, 2011
Messages
14
Let's say I have Column Arow 1 to 5 is where I put in the values needed for a certain calculation


A1: 01/01/1991
A2: 01/01/1992
A3: 01/01/1993
A4: 01/011994
A5: 01/01/1995


As you can see A1 to A5 are supposed to be dates, but I mistyped the value in A4


is there anyway for Excel to notify me if I mistyped a date value?
can I make Excel hilight A4 in red if I made this error?
or any other kind of suggestions?


please help


thank you for reading
 
Last edited:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
=CELL("format",A1)
D4 is date

Unfortunately, that only tells you what the cell is formatted as, it does not tell you what is in the cell. For the formula you posted, type your name in A1 and recalculate the sheet... you will still see the formula returning D4.
 
Upvote 0
@ olargee -i think my given formula to the original question(i can remember now) is not applicable now to your new querry.

is there anyway for Excel to notify me if I mistyped a date value?
can I make Excel hilight A4 in red if I made this error?
or any other kind of suggestions?

if you would like to display message in cell some like like "true"

udf..
Function cdate(cd As Date)
cdate = IsDate(cd)
End Function
=cdate(A1)----but CF is the best method to notify you of these dates.....
 
Upvote 0
if you would like to display message in cell some like like "true"

udf..
Function cdate(cd As Date)
cdate = IsDate(cd)
End Function
=cdate(A1)----but CF is the best method to notify you of these dates.....

Sorry to disagree with you again, but IsDate is not a reliable way to verify that an entry is a date or not. I don't know which month/day order is used at your locale, but no matter which, one of these should be "wrong" for a date input, but IsDate will report True for both of them...

Code:
MsgBox IsDate("3/21/2012")
MsgBox IsDate("21/3/2012")
That means if a user inputs the date in the wrong order when the day number is 12 or less, the IsDate will still report True for it. Just to make matters worse, if the user inputs a date using a 2-digit year number, then there are more ways to input it wrong and still have IsDate report True. Consider these...

Code:
MsgBox IsDate("3/21/12")
MsgBox IsDate("3/12/21")
MsgBox IsDate("12/3/21")
MsgBox IsDate("12/21/3")
MsgBox IsDate("21/3/12")
MsgBox IsDate("21/12/3")
The only reliable way to take a date from a user is to put a calendar control in front of him/her and let them click the day number from a month view within a specified year. There are such controls posted on the web, so you will have to do a search for one that meets your needs.
 
Upvote 0
Hi

I agree with Rick and I think that none of the methods posted verifies that the input is a date.

The function Day() just cares that the number value in the cell can be converted to a date. If you write in the cell 1 or 1,000,000 Day() will work ok, since they can be both converted to dates (up to the year 9999).

VBA's IsDate() does not say if it is a date, it says that it's an expression that can be converted to a date value. For ex. if you write the string "2012-01-01" in the cell as a text value IsDate() will return true.

I think that a way to verify if a value in a cell is a date value (I mean a posteriori, after the input process), is to use the 2 vba functions that check the type of a value.

For ex., for A1:
Code:
MsgBox TypeName(Range("A1").Value) = "Date"
Code:
MsgBox VarType(Range("A1").Value) = vbDate
I don't think that there is a Date type in the worksheet, so my guess is that these type checking function will check both that the cell has a number value and it is formatted as Date.

Following this reasoning I think we can check that in the worksheet the value is a date using an AND() with vlady's Cell() and IsNumber().
 
Upvote 0
Let's say I have Column Arow 1 to 5 is where I put in the values needed for a certain calculation


A1: 01/01/1991
A2: 01/01/1992
A3: 01/01/1993
A4: 01/011994
A5: 01/01/1995


As you can see A1 to A5 are supposed to be dates, but I mistyped the value in A4


is there anyway for Excel to notify me if I mistyped a date value?
can I make Excel hilight A4 in red if I made this error?
or any other kind of suggestions?


please help


thank you for reading
If the range of cells is to contain dates only and if these dates must fall within a specific date range then you could use conditional formatting or data validation to alert you to invalid entries.
 
Upvote 0

Forum statistics

Threads
1,226,498
Messages
6,191,376
Members
453,655
Latest member
lasvegasbuffet

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