[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

excel date check

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
brianpowell
Asked:
brianpowell
  • 3
  • 2
1 Solution
 
brianpowellAuthor Commented:
will be using:  =DATEDIF(B10,B11,"d")

for the diff formula.
0
 
Shaun KlineLead Software EngineerCommented:
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
 
brianpowellAuthor Commented:
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
 
Shaun KlineLead Software EngineerCommented:
=IF(OR(ISERR(DATEVALUE(B10)),ISERR(DATEVALUE(B11))), "Error", "Good")
0
 
brianpowellAuthor Commented:
sorry for the delay.

thanks!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now