[Solved] IFERROR() help

[Deepak chawla]

Hi
If in A6, B6, C6 there is a date, how can we calc and get the days from using formula
=IF(iferror(datedif(C6;D6;"D");0)=0;datedif(C6;B6;"D");datedif(C6;D6;"D"))

i tried on open office but getting error

Please help

[rajew]

Hey,

In AOO calc there are no IFERROR and DATEDIF functions, hence You get the #NAME? error probably.
To get the same behaviour from a Calc formula You would have to use something like:

Code: Select all

=IF((IF(ISERROR(DAYS(C6;D6));0;DAYS(C6;D6))=0);DAYS(C6;B6);DAYS(C6;D6))

[Deepak chawla]

PLEASE FIND BELOW EXAMPLE

Code: Select all

      (A)            (B)            (C)                (D)
CURRENT DATE      OTHER DATE       DATE            RESULT
22-Nov-16            10-Nov-16      11-Nov-16          1
22-Nov-16            15-Oct-16                        42658
22-Nov-16            20-Oct-16                       -42663

While using formula i am get result in first row but not it rest row
=IF((IF(ISERROR(DAYS(D7;C7));0;DAYS(D7;C7))=0);DAYS(C7;B7);DAYS(D7;C7))

I need to get days if there is data in (C) means (C)-(B) else days from (B) - (A)

[floris v]

Please tell us what you want to achieve, in English; forget the math.

[rajew]

I need to get days if there is data in (C) means (C)-(B) else days from (B) - (A)

Well, based on that I would say something like:

Code: Select all

=ABS(IF($C7="";DAYS($B7;$A7);DAYS($C7;$B7)))

should be enough, if You only have a date or nothing in the C column. This will return the number of days between either A and B or B and C, if there is anything in the C column.
You can drop the ABS part if You want to see negative numbers aswell, so:

Code: Select all

=IF($C7="";DAYS($B7;$A7);DAYS($C7;$B7))

If for some unholy reason You really need to check if the formula would produce an error You could go with:

Code: Select all

=IF(ISBLANK($C6)OR(ISERROR(DAYS($C6;$B6)));DAYS(B6;A6);DAYS(C6;B6))

[Lupp]

(DATEDIF is just one of the many useless functions commercial competitors introduced.)

Please attach your examples included in a reduced but relevant ("speaking") spreadsheet document (.ods). It should also contain (clearly marked) the results you expect and the formulae you already tried without success. Otherwise helpers will have to waste some time to simply understand you, and get at the start.

The orgiginal question already contains a grave inconsistency, imo. The result seems to be expected in column D but the formula given to explain the intentions references the cell expected to contain the result twice. A circular reference surely doesn't make sense in this context.

The most recent explanation refers to differences that will be negative in two rows based on the given examples. I would suggest you clearly tell us if this is what you want. In addition the condition for using the first mentioned difference now is

...if there is data in (C)...

I suppose you expect the "data" to be numeric and interpreted as a date.

See attached.

[Deepak chawla]

thanks LUPP!
Its working

[Deepak chawla]

(A) (B) (C) (D)
CURRENT DATE SALE DATE BILL DATE AGEING
21-Nov-17 10-Nov-17 01-Nov-17 SOLD
21-Nov-17 01-Nov-17 -20
21-Nov-17 01-Nov-17 -20

[Deepak chawla]

Hi,
I need the stock Ageing report in open office
If in column A contains Current Date, B Contains Sale Date, C contains Bill Date, In D i need Stock Ageing number of days stock kept. If in case sold then In Column B to insert the date and print in D SOLD