We help IT Professionals succeed at work.

Excel Formula

Rrave26
Rrave26 asked
on
337 Views
Last Modified: 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?
Comment
Watch Question

Commented:
I've seen cases where empty cells are not "" so that could be your problem here.  One way to solve that is to append "" in the test:

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

or you could try ISBLANK:

=IF(ISBLANK(AX283), ...

Author

Commented:
Ok, here is my formula:  =IF(ISBLANK(AX276),0,IF(AR276>AX276,0,AX276-AR276)).  Tried that and I get an Value error message.  Cell AX is null so I should get a value of 0 correct?
CERTIFIED EXPERT

Commented:
Point of clarification...

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.

Author

Commented:
They are formatted as date time values.  They are all formated as mm/dd/yyyy h:mm.
CERTIFIED EXPERT

Commented:
if you put these formulas in some unused cells... what displays for each?

=ISBLANK(AX276)
=AR276>AX276
=AX276-AR276

Author

Commented:
Here are my results

=ISBLANK(AX276)  False
=AR276>AX276 False
=AX276-AR276  #VALUE
CERTIFIED EXPERT

Commented:
=AX276-AR276  #VALUE

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 (at least which ones Excel not recognize as dates).

=IF(NOT(ISERROR(DATEVALUE(TEXT(AR276,"mm/dd/yyyy")))),"valid date","invalid date")
=IF(NOT(ISERROR(DATEVALUE(TEXT(AX276,"mm/dd/yyyy")))),"valid date","invalid date")

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).

Commented:
=ISBLANK(AX276)  False

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

Author

Commented:
Here are the results:

=IF(NOT(ISERROR(DATEVALUE(TEXT(AR276,"mm/dd/yyyy")))),"valid date","invalid date")  "Valid"
=IF(NOT(ISERROR(DATEVALUE(TEXT(AX276,"mm/dd/yyyy")))),"valid date","invalid date")  "INvalid"

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
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
This fixed my issue.  Thanks for the help.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.