Solved

excel date check

Posted on 2014-03-27
5
266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 26

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 26

Accepted Solution

by:
Shaun Kline earned 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article describes how to add a user-defined command button to the Windows 7 Explorer toolbar.  In the previous article (http://www.experts-exchange.com/A_2172.html), we saw how to put the Delete button back there where it belongs.  "Delete" is …
With most software applications trying to cater to multiple user needs nowadays, the focus is to make them as configurable as possible. For e.g., when creating Silverlight applications which will connect to WCF services, the service end point usuall…
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

729 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