convert cell with 5 digits into text saying "Vacant&quo

[DKgirlie]

Hi,

I am hoping sopmeone can help me oout - i have a report i run with staff members key dates (recruitment spreadsheet). So i have 2 main columns :

Sign Off Date (A1)--> This is the date the manager authorizes the recruitment
Offer Date (B1)--> This is the date the recruiter makes an offer

Basically what we want to know is the "time to fill" - how long it takes from sign off date to offer date.
I can calculate this easily by a simple subtraction =A1-B1
This works ok if both cells have a date - the probl;em i am having is when the Offer date cell is empty i get a 5 digit negative number - for example A1 is 15/07/2005 and B1 is empty - the result is -38548.

What is the easiest way to either make this cell stay blank or say "Vacant"?

Can i run a macro that looks for any 5 digit negative number and converts it into the words "vacant" ?

Any other suggestions greatly appreciated !

Cheers,
Deb

 

[Aladin Akyurek]

Re: convert cell with 5 digits into text saying "Vacant

Hi,

I am hoping sopmeone can help me oout - i have a report i run with staff members key dates (recruitment spreadsheet). So i have 2 main columns :

Sign Off Date (A1)--> This is the date the manager authorizes the recruitment
Offer Date (B1)--> This is the date the recruiter makes an offer

Basically what we want to know is the "time to fill" - how long it takes from sign off date to offer date.
I can calculate this easily by a simple subtraction =A1-B1
This works ok if both cells have a date - the probl;em i am having is when the Offer date cell is empty i get a 5 digit negative number - for example A1 is 15/07/2005 and B1 is empty - the result is -38548.

What is the easiest way to either make this cell stay blank or say "Vacant"?

Can i run a macro that looks for any 5 digit negative number and converts it into the words "vacant" ?

Any other suggestions greatly appreciated !

Cheers,
Deb

Change

=A1-B1

to

=IF(COUNT(A1:B1)=2,A1-B1,"")

 

[SteveO59L]

=if(b1="","Vacant",A1-B1)

 

[DKgirlie]

Awesome thanks so much !!!!