[Solved] =IF ISBLANK and if not ISTEXT and if not::

[pherriot]

Hi People.
Using a previous users formula I have attempted to be smart....and havent been.
The formula is "trying" to check for:
BLANK and if so return that
TEXT and if so return that
if none of the above then take value from sheet "WK 1.C21" and subtract from "Wk 1.D21"

.....and heres is my attempt:
=IF(ISBLANK($'Wk 1'.C21);"";IF(ISTEXT($'Wk 1'.C21)$'Wk 1'.D21-$'Wk 1'.C21))
...I get the "Err:509"

FYI:
Wk 1'.C21 is a time
Wk 1'.D21 is a time
Any help is appreciated.
Cheers.

[Villeroy]

There is a semicolon missing between )$

[pherriot]

Hi, thanks for the reply. Cant believe that one slipped....

However the subtraction is not working - Wk 1'.D21-$'Wk 1'.C21 instead it returns a result of 00:00

An the IFTEXT appears to not be working (please see images) i.e. the text is not passed back but instead results in the error #VALUE!

=IF(ISBLANK($'Wk 1'.C21);"";IF(ISTEXT($'Wk 1'.C21);$'Wk 1'.D21-$'Wk 1'.C21))

[pherriot]

.....it works fine when theres no BLANK and TEXT checking

=($'Wk 1'.D21<$'Wk 1'.C21)+$'Wk 1'.D21-$'Wk 1'.C21 returns the time difference between the 2 values "08:15" hrs.

[RusselB]

After the ISTEST check you only have one parameter, which is the parameter you have specified to be returned when the ISTEXT is false.

Code: Select all

=IF(ISBLANK($'Wk 1'.C21);"";IF(ISTEXT($'Wk 1'.C21);$'Wk 1'.C21;$'Wk 1'.D21-$'Wk 1'.C21))

[pherriot]

Thanks RusselB. Very much appreciated.
Cheers.