Formula sometimes returning correct answer and sometimes returning 0

**redimp** · 09-09-2010, 05:22 AM

Hi, after the brilliant help last time I have another query:

I have entered the following nest of formulas:

=IF(('Current Employment'!N2-'Personal details '!O2)/365<1,"",(ROUNDDOWN(IF('Personal details '!N2="",0,IF('Personal details '!O2="",0,('Current Employment'!N2-'Personal details '!O2)/365)),0)))

and then copied and pasted it down a column. It is to compare two days and then return the difference as whole completed years. Sometimes, it returns a correct result and sometimes 0 when the result should be an integer even when the two correct dates are entered in the correct place. It has me totally foxed.

Sorry I cannot attach the sheet as it contains confidential information.

Edit to add example:

I have done a simple

=('Current Employment'!N170-'Personal details '!O170)/365

and it has returned the correct answer of 9.34 but the above formula as

=IF(('Current Employment'!N170-'Personal details '!O170)/365<1,"",(ROUNDDOWN(IF('Personal details '!N170="",0,IF('Personal details '!O170="",0,('Current Employment'!N170-'Personal details '!O170)/365)),0)))

is returning a value of 0

**romperstomper** · 09-09-2010, 05:30 AM

What are in N170 and O170 and do you have calculation set to automatic?

**redimp** · 09-09-2010, 05:40 AM

N is a Termination Date and O is a start date. It is to calculate periods of continuous service.

The return cell is formatted as number with no decimal places.

And yes, it is set to automatic.

In case it is relevant it is 2003 SP3.

**romperstomper** · 09-09-2010, 05:43 AM

Shouldn't it be:
=IF(('Current Employment'!N170-'Personal details '!O170)/365<1,"",(ROUNDDOWN(IF('Current Employment'!N170="",0,IF('Personal details '!O170="",0,('Current Employment'!N170-'Personal details '!O170)/365)),0)))
?