software

Hi...hope you can help.

I have a few worksheets with some personnel info. I put all of this in
one worksheet. One of the items I have listed is their promotion date.

On another worksheet, I copy the promotion date so I can do a
calculation (=Personnel!E6). The formula I use to calculate their time
in grade is
=IF(E8lt;gt;quot;quot;,DATEDIF(E8,TODAY(),quot;yquot;),quot;quot;)

The problem I have, if the promotion date in the quot;Personnelquot; sheet is
blank, the formula is returning a value of 106 instead of being blank
until I enter a promotion date on the quot;Personnelquot; worksheet.

Cell E6 on the quot;personnelquot; sheet is the promotion date
Cell E8 on quot;sheet2quot; is copied from the personnel sheet
Cell E9 on quot;sheet2quot; contains the above formula, and it's returning a
value of 106.

Is there a way to fix that?
Thanx--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: www.excelforum.com/member.php...oamp;userid=24735
View this thread: www.excelforum.com/showthread...hreadid=498919
That is because excel see's a blank cell as 0 Jan 1900 so.

Instead of:

=Personnel!E6
use
=IF(Personnel!E6lt;gt;quot;quot;,Personnel!E6,quot;quot;)

HTH
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=498919Hi

One way would be to just test that E8gt;0
=IF(E8gt;0,DATEDIF(E8,TODAY(),quot;yquot;),quot;quot;)
If E6 is blank, then copying E6 would return 0 which Excel would
interpret as being day 0 (01 Jan 1900) hence the Datedif calculation
returns 106 years.--
Regards

Roger Govierquot;Ltat42aquot; gt; wrote
in message ...
gt;
gt; Hi...hope you can help.
gt;
gt; I have a few worksheets with some personnel info. I put all of this in
gt; one worksheet. One of the items I have listed is their promotion date.
gt;
gt; On another worksheet, I copy the promotion date so I can do a
gt; calculation (=Personnel!E6). The formula I use to calculate their time
gt; in grade is
gt; =IF(E8lt;gt;quot;quot;,DATEDIF(E8,TODAY(),quot;yquot;),quot;quot;)
gt;
gt; The problem I have, if the promotion date in the quot;Personnelquot; sheet is
gt; blank, the formula is returning a value of 106 instead of being blank
gt; until I enter a promotion date on the quot;Personnelquot; worksheet.
gt;
gt; Cell E6 on the quot;personnelquot; sheet is the promotion date
gt; Cell E8 on quot;sheet2quot; is copied from the personnel sheet
gt; Cell E9 on quot;sheet2quot; contains the above formula, and it's returning a
gt; value of 106.
gt;
gt; Is there a way to fix that?
gt; Thanx
gt;
gt;
gt; --
gt; Ltat42a
gt; ------------------------------------------------------------------------
gt; Ltat42a's Profile:
gt; www.excelforum.com/member.php...oamp;userid=24735
gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=498919
gt;