Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

excel date check

Posted on 2014-03-27
5
Medium Priority
?
273 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 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

This article describes a technique for converting RTF (Rich Text Format) data to HTML and provides C++ source that does it all in just a few lines of code. Although RTF is coming to be considered a "legacy" format, it is still in common use... po…
What my article will show is if you ever had to do processing to a listbox without being able to just select all the items in it. My software Visual Studio 2008 crystal report v11 My issue was I wanted to add crystal report to a form and show…
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…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

618 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