Adding Nested IFs gives incorrect Value

[Malk]

I am having difficulty finding any error with my formula.

This formula is supposed to give a time in days:minutes:hours format. When I run this formula as it is I get a result of 1:00:00 when it should be 32:00:00. If I delete the last nested IF (after the third "+") the formula yields the correct result of 24:00:00. I have also replaced the last nested IF with a "7" and got 31:00:00. If I replace it with an "8" i get the incorrect 1:00:00 again. If I replace it with a 9 I get 2:00:00. Why is this giving wrong return values when and after I reach a value of 32?

Formula in question is in Column F of the sample. I will add that when Inputting values into Column B they are in this format: "1/1/1900 12:00:00 AM"

Edit: Changed to sample per request.

[JohnTopley]

Attach a sample workbook (not image).so that we do not have to manually key in your data to do a testing.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Malk]

Attach a sample workbook (not image).so that we do not have to manually key in your data to do a testing.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Edited and attached file to OP

[JohnTopley]

Try

=((IF(A1="Available",E1,IF(A1="Upgrading",E1-(NOW()-B1),0)))+(IF(A2="Available",E1,IF(A2="Upgrading",E1-(NOW()-B2),0)))+(IF(A3="Available",E1,IF(A3="Upgrading",E1-(NOW()-B3),0)))+(IF(A4="Available",E1,IF(B4="Upgrading",E1-(NOW()-B4),0))))/24


Or

correctly format E1 as TIME not Date/Time and enter as 08:00:00

your formula will then be OK.

=((IF(A1="Available",E1,IF(A1="Upgrading",E1-(NOW()-B1),0)))+(IF(A2="Available",E1,IF(A2="Upgrading",E1-(NOW()-B2),0)))+(IF(A3="Available",E1,IF(A3="Upgrading",E1-(NOW()-B3),0)))+(IF(A4="Available",E1,IF(B4="Upgrading",E1-(NOW()-B4),0))))

returns 1:8:00 as d:h:mm or 32:00 as [h]:mm

[JohnTopley]

Alternative is .

=(SUMPRODUCT(($A$1:$A$4="Available")*$E$1)+SUMPRODUCT(($A$1:$A$4="Upgrading")*($E$1-(NOW()-$B$3:$B$6))))