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

#### DKgirlie

##### New Member
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

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

##### MrExcel MVP
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

##### Well-known Member
=if(b1="","Vacant",A1-B1)

#### DKgirlie

##### New Member
Awesome thanks so much !!!!

Replies
1
Views
174
Replies
9
Views
316
Replies
2
Views
294
Replies
6
Views
236
Replies
3
Views
138

1,191,686
Messages
5,988,068
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back