=IF(AX283 & ""="", ...

or you could try ISBLANK:

=IF(ISBLANK(AX283), ...

Solved

Posted on 2014-08-04

I have a a spreadsheet where I am calculating elapsed time of calls. I have one cell called Service avaiable date and time, cell AX, and another called IM Engaged date and time, cell AR. Here are the scenarios I can run into:

Scenario #1

Service availble date, AX, can be null. In this case the result should be 0:00:00

Scenario #2

IM Engaged date,cell AR, 07/16/2014 18:04 is greater then the service availble date, cell AX, 07/16/2014 17:35. Result should be 0:00:00

Scenario #3

Service availble date,cell AX, is greater, 07/16/2014 18:04 then IM Enaged date,cell AR, 07/16/2014 17:35. Result should be 0:29:00.

I tried a nested if formula, =IF(AX283="",0,IF(AR283>AX283,0,AX283-AR283) with no success. What am I missing here?

Scenario #1

Service availble date, AX, can be null. In this case the result should be 0:00:00

Scenario #2

IM Engaged date,cell AR, 07/16/2014 18:04 is greater then the service availble date, cell AX, 07/16/2014 17:35. Result should be 0:00:00

Scenario #3

Service availble date,cell AX, is greater, 07/16/2014 18:04 then IM Enaged date,cell AR, 07/16/2014 17:35. Result should be 0:29:00.

I tried a nested if formula, =IF(AX283="",0,IF(AR283>AX

11 Comments

=IF(AX283 & ""="", ...

or you could try ISBLANK:

=IF(ISBLANK(AX283), ...

You are checking AR283>AX283.

The answer to whether one value is greater than another is true/false, will depend to a large extent on the data in the cell. Is the data in your AR/AX cells formatted as date/time values or are they formatted as text values?

If they are formatted as text, you might be determining greater than or less than in terms of alphabetical order, rather than date/time order.

In addition if the cells are formatted as text... when you subtract the values... that will result in an error.

=ISBLANK(AX276)

=AR276>AX276

=AX276-AR276

This line would indicate that both cells do not contain values that excel recognizes as a date/time field.

Can you post back the results for these formulas? -- trying to figure which cell or cells do not contain dates

=IF(NOT(ISERROR(DATEVALUE(

=IF(NOT(ISERROR(DATEVALUE(

In the scenario you are looking at for AR276, AX276 do you expect isblank(AX276) to return true?

(This might be easier to debug if you are able to post an example of your workbook that contanis the error).

What do you see in AX276?

And what is the formula in AX276? For formulas, Excel may interpret a "blank" as zero (0)

=IF(NOT(ISERROR(DATEVALUE(

=IF(NOT(ISERROR(DATEVALUE(

For the scenario with AR276, AX276 I did expect isblank(AX276) to return true. But now I'm thinking that a "" doesn't return a value.

I have attached my spreadsheet for review. I am looking at the IM Raw Data tab.

Prouduction-IM-METRICS-T-V03.xlsm

=IF(AX276="",0,IF(AR276>AX

The problem is... AX276 is not blank, because it contains a formula. --- Even though the result of the formula is a blank.

Using the above format, you are specifically checking to see if a result of the formula is blank, rather than the cell being blank. (slightly different nuance).

Note... this looks very close to the formula in your original post that you already tried... only difference I can see, is that you were missing a trailing parenthesis.

This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**24** Experts available now in Live!