Nested IF statement

[Paul long]

Hi Guys,
New to the board.
I'm struggling with a nested IF on a sheet that logs fork lift repairs.
Column K allows me to mark with a "Y" if the job is complete, and if so I want column N to calculate the number of days between the reported date and the repaired date, if it's not been repaired yet, then calculate the number of days between today and the date it was reported.

In column N I have =IF(K4="Y",(L4-A4),(A2-A4)) As you can see it works if the job is completed, but if there is no "Y" in K, then I want it to calculate days to today since we logged it. As you can see in N5, it doesnt like it.

 

[FormR]

Hi, welcome to the board!

The #VALUE! error would indicate that either A2 or A5 are text rather than dates - do you have a formula in cell A2 that is returning today's date? If so, what is it?

 

[Paul long]

Hi, welcome to the board!

The #VALUE! error would indicate that either A2 or A5 are text rather than dates - do you have a formula in cell A2 that is returning today's date? If so, what is it?

Thanks for that,
Column A & L are formatted as dates.
A2 has the formula =TODAY()

 

[FormR]

To help diagnose, if you put these formulas in spare cells, what do they return?

=ISNUMBER(A2)
=ISNUMBER(A5)

 

[Paul long]

To help diagnose, if you put these formulas in spare cells, what do they return?

=ISNUMBER(A2)
=ISNUMBER(A5)

Both come back as TRUE

 

[awoohaw]

I think you need to use Absolute reference for cell A2:

=IF(K4="Y",L4-A4,$A$2-A4)

And are you sure column A is a date formatted column? (Select column A, change format to "General", do the dates get converted to numbers?)

 

[Paul long]

I think you need to use Absolute reference for cell A2:

=IF(K4="Y",L4-A4,$A$2-A4)

And are you sure column A is a date formatted column? (Select column A, change format to "General", do the dates get converted to numbers?)

Tried switching it and it changed to numbers, changed it back to this...

 

[FormR]

I think you need to use Absolute reference for cell A2:

I think this is likely the answer. Copy and paste the formula that is returning the #VALUE! in a reply here.

 

[awoohaw]

would you be kind enough to post your data in a table or use the xl2bb add in (preferable). I cannot read your image in post 1, to try to recreate your scenario.

 

[Paul long]

I think this is likely the answer. Copy and paste the formula that is returning the #VALUE! in a reply here.

=IF(K5="Y",(L5-A5),(A3-A5))

here you go