If function to extract a number from a cell

[Ozzie2842]

Hello,
I have a column with the year that an employee was hired. I'm attempting to include a column with the decade the employee was hired, e.g., Year Hired is 1981, the decade would then be 1980. I'm using this formula, but I keep getting a "False" result. Please help me find my error.

CELL B2 - 1981
CELL D2 - 1980

=IF(LEFT(B2,3)="8",$D$2,"FALSE"))

 

[jeffreybrown]

How about...

=IF(MID(B2,3,1)="8",$D$2,FALSE)

You could use this an eliminate putting 1980 in cell D2

=IF(MID(B2,3,1)="8",(LEFT(B2,3)&0)+0,FALSE)

 

[Ozzie2842]

You are an Excel Ninja, thanks

 

[jeffreybrown]

You're welcome and glad it worked for you

 

[T. Valko]

Hello,
I have a column with the year that an employee was hired. I'm attempting to include a column with the decade the employee was hired, e.g., Year Hired is 1981, the decade would then be 1980. I'm using this formula, but I keep getting a "False" result. Please help me find my error.

CELL B2 - 1981
CELL D2 - 1980

=IF(LEFT(B2,3)="8",$D$2,"FALSE"))

Another way...

=FLOOR(B2,10)

 

[Scott Huish]

Also:

=ROUNDDOWN(B2,-1)

 

[jeffreybrown]

Thanks guys...

Much better...built in flexibility