1

I created an excel formula that convert a string into a date.

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

It's working as expected when a value is specified but blank cell are giving me an error so I added an IF to handle those cells.

=IF(ISBLANK(A1),"",DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)))

The problem with the IF keyword is that both true and false parts seems to be evaluated. In this specific case the LEFT / MID / RIGHT keyword will each raise an error even if the "" should be used.

Is is possible to tell the IF keyword to evaluate only the value that will be used based on it's condition?

P.S. I had to convert the formula from a french version of Excel... if there's any typo please let me know.

2
  • 1
    does A1 contain a formula that returns "" IF does not care if the side it does not use evaluated to an error. Sep 12 '19 at 18:38
  • 1
    And you seem to missing a ) on both your examples. Sep 12 '19 at 18:39
2

Prevent false condition from being evaluated

False conditions are not evaluated in Excel formulas.

You likely have cells looking to be blank, but not being really blank (e.g. contain formula or ')

Instead of ISBLANK, use A1=""

1
  • Indeed, some cells had some invisible characters in it. Sep 12 '19 at 20:28
0

I was able to have success using the same formula as yours

=IF(ISBLANK(Input_Text), "", DATE(LEFT(Input_Text,4), MID(Input_Text, 5, 2), RIGHT(Input_Text, 2)))
  • When Input_Text is 20100203, I get 2/3/2010
  • When Input_Text is blank, I get a blank cell

Not sure why your version doesn't work. Perhaps there is some other problem?

0

Try IFERROR Function to avoid display of Error Type of Messages

=IFERROR(Formula,"")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.