#Value! message in formula

C

Carl

I have a spreadsheet of various employee data. In one column, I'm getting a '#Value!' message in Column I if the employee has never been promoted past the entry rank. Here's the layout:

Column F = Date of commission
Column G = Seniority w/ agency: =DATEDIF(F2,NOW(), "y") & " years, "& DATEDIF(F2,NOW(), "ym") & " months, "& DATEDIF(F2,NOW(),"md") & " days"
Column H = Date of promotion
Column I = Rank Seniority: =DATEDIF(H2,NOW(), "y") & " years, "& DATEDIF(H2,NOW(), "ym") & " months, "& DATEDIF(H2,NOW(),"md") & " days"

When the employee has never been promoted, the ‘Rank Seniority’ shows
#VALUE!

How do I change that error value into the value that is in Column G (Seniority w/ Agency) while still showing the Rank Seniority of those promoted?
 
J

JulieD

Hi Carl

try
column I
=IF(H2="",G2,DATEDIF(H2,NOW(), "y") & " years, "& DATEDIF(H2,NOW(), "ym") &
" months, "& DATEDIF(H2,NOW(),"md") & " days")
this assums that H2 is blank if someone has not been promoted.
Cheers
JulieD


Carl said:
I have a spreadsheet of various employee data. In one column, I'm getting
Click to expand...
a '#Value!' message in Column I if the employee has never been promoted past
the entry rank. Here's the layout:
Column F = Date of commission
Column G = Seniority w/ agency: =DATEDIF(F2,NOW(), "y") & " years, "&
Click to expand...
DATEDIF(F2,NOW(), "ym") & " months, "& DATEDIF(F2,NOW(),"md") & " days"
Column H = Date of promotion
Column I = Rank Seniority: =DATEDIF(H2,NOW(), "y") & " years, "&
Click to expand...
DATEDIF(H2,NOW(), "ym") & " months, "& DATEDIF(H2,NOW(),"md") & " days"
When the employee has never been promoted, the 'Rank Seniority' shows
#VALUE!

How do I change that error value into the value that is in Column G
Click to expand...
(Seniority w/ Agency) while still showing the Rank Seniority of those
promoted?
 
C

Carl

Worked like a champ! Thanks a lot. My two brain cells were hurting trying to figure it out.

Carl
 
C

Carl

Ooops. Still got a problem after all. It works properly when there is no date in column H. If there is a date in column H, I now get a '#NAME?' value in the cell in column I.

Carl
 
D

Dave Peterson

That name error can happen if you make a typo in the formula. (New() instead of
Now().)

Try copying JulieD's formula form the newsgroup post and paste it in the formula
bar when I2 selected.

(it worked fine for me)
 
Top