display yrs of Svc
 

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(E8<"",DATEDIF(E8,TODAY(),"y"),"")

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

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

Is there a way to fix that?
Thanx


--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=498919

display yrs of Svc
 

That is because excel see's a blank cell as 0 Jan 1900 so.

Instead of:

=Personnel!E6
use
=IF(Personnel!E6<"",Personnel!E6,"")

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=498919

display yrs of Svc
 

Hi

One way would be to just test that E80
=IF(E80,DATEDIF(E8,TODAY(),"y"),"")
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 Govier


"Ltat42a" wrote
in message ...

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(E8<"",DATEDIF(E8,TODAY(),"y"),"")

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

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

Is there a way to fix that?
Thanx


--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
View this thread:
http://www.excelforum.com/showthread...hreadid=498919