?
Solved

excel date check

Posted on 2014-03-27
5
Medium Priority
?
282 Views
Last Modified: 2014-03-31
all -

I have two cells  B10 and B11
I need to check both cells for valid dates prior to calculating the difference between the two.

What is the best in cell formula to check for a valid date. e.g. blanks, '-' ,  or NA, '2342jflsd'

thanks

ps

Cell B9 will have the formula for data checking and also will be the cell that holds the number of days difference between the two

thanks for your time in advance!
0
Comment
Question by:brianpowell
  • 3
  • 2
5 Comments
 
LVL 1

Author Comment

by:brianpowell
ID: 39959508
will be using:  =DATEDIF(B10,B11,"d")

for the diff formula.
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 39959524
You can use the ISERR and DATEVALUE functions together to determine if a string is a valid date:

=IF(ISERR(DATEVALUE("11/11/11")), "Error", "Good")
=IF(ISERR(DATEVALUE("111111")), "Error", "Good")
0
 
LVL 1

Author Comment

by:brianpowell
ID: 39959694
How do I 'OR' the statements:

example:

B9 = needs to check both B10 and B11

In your above example:
This code will be in cell B9 and error needs to be return if B10 OR B11 is false.
=IF(ISERR(DATEVALUE("11/11/11")), "Error", "Good")
0
 
LVL 27

Accepted Solution

by:
Shaun Kline earned 2000 total points
ID: 39959854
=IF(OR(ISERR(DATEVALUE(B10)),ISERR(DATEVALUE(B11))), "Error", "Good")
0
 
LVL 1

Author Closing Comment

by:brianpowell
ID: 39967360
sorry for the delay.

thanks!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As more and more people are shifting to the latest .Net frameworks, the windows presentation framework is gaining importance by the day. Many people are now turning to WPF controls to provide a rich user experience. I have been using WPF controls fo…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question