Why is IF returning #VALUE!? Seem correct to me.

[Camel123]

Hi,

Below is part of the formula. The error is in the beginning.

When evaluating; first logic test= FALSE, instead of running the formula in the "FALSE" section, it returns #VALUE !. Why? I can not see anything wrong.



=IF(D4="";"manual";IF(DATEVALUE(INDEX('planned'!J:J;MATCH('booked'!D4;'planned'!E:E;0)))<DATEVALUE($E$2)... formula continues...

 

[Scott T]

What is the index/match returning? If the DATEVALUE does not know what date the text is then you will get a value error.

 

[DanteAmor]

You can put the complete formula and a sample of your data. And from those data what is the result you expect.

 

[Camel123]

I’ll do that once back at work!

 

[DanteAmor]

Great! let me know if you have any doubts

 

[Camel123]

So here is the whole formula

Sheet "booked accruals"
D4= purchase order number to be found in the other sheet
E2= current month (manually set)

Sheet "accruals planning"
I:I= start date
J:J end date
K:K datedif between start and end date in months


=IF(D4="";"manual";IF(DATEVALUE(INDEX('Accruals planning'!J:J;MATCH('Booked accruals'!D4;'Accruals planning'!E:E;0)))<DATEVALUE($E$2);INDEX('Accruals planning'!K:K;MATCH('Booked accruals'!D4;'Accruals planning'!E:E;0));IF(DATEVALUE(INDEX('Accruals planning'!I:I;MATCH('Booked accruals'!D4;'Accruals planning'!E:E;0)))=DATEVALUE($E$2);"1";IF(DATEVALUE(INDEX('Accruals planning'!I:I;MATCH('Booked accruals'!D4;'Accruals planning'!E:E;0)))>DATEVALUE($E$2);"future";IF(INDEX('Accruals planning'!I:I;MATCH('Booked accruals'!D4;'Accruals planning'!E:E;0))="";"manual";DATEDIF(INDEX('Accruals planning'!I:I;MATCH('Booked accruals'!D4;'Accruals planning'!E:E;0));'Booked accruals'!$E$2;"M"))))))

 

[Camel123]

Here's a snap of all evaluation steps:

https://imgur.com/rXJ0lpR

 

[RoryA]

It looks like the returned value is already a date, so remove the DATEVALUE() function.

 

[Camel123]

I thought DATEVALUE converted dates to numerical values and not the opposit.. well, learnt something new at least! Thank you Rory again for your support, and to all other people trying to help me out <3

It works now!

 

[RoryA]

No, DATEVALUE is for converting a text representation of a date to a true date value. (In Excel all dates are stored as numbers anyway)