Formula not working......and I can't see why!

[TheWennerWoman]

Cells B4 and B5 on my worksheet allow the user to enter a year and period. So, typically they'll enter 2021 in B4 and 7 in B5 (relating to period 7 in 2021, i.e July).

I have this formula in B6;

=B4&IF(B5<"10","0"&B5,B5)

which drives some SQL code in my macro. In the above example, 202107 is correctly returned.

However, if they enter 2021 in B4 and 10 in B5, my SQL code returns no data and I can see why - in B6, my formula isn't working, it's returning 2021010.

What am I doing wrong? it works for 1-9 but not 10 (and presumably 11 and 12)!!

 

[KOKOSEK]

B​	C​	D​
8​	08​	08​
9​	09​	09​
10​	010​	10​
11​	011​	11​

B​	C​	D​
8​	=IF(B8<"10","0"&B8,B8)​	=IF(B8<10,"0"&B8,B8)​
9​	=IF(B9<"10","0"&B9,B9)​	=IF(B9<10,"0"&B9,B9)​
10​	=IF(B10<"10","0"&B10,B10)​	=IF(B10<10,"0"&B10,B10)​
11​	=IF(B11<"10","0"&B11,B11)​	=IF(B11<10,"0"&B11,B11)​

 

[GraH]

"10" mean Excel reads this as text, not a number. Maybe this works?

=B4&IF(VALUE(B5)<10,"0"&B5,B5)

 

[offthelip]

try this:
=B4&IF(B5<10,"0"&B5,B5)

 

[FormR]

Hi, you could also try this alternative in B6.

=B4&TEXT(B5,"00")

 

[TheWennerWoman]

Thanks all, much appreciated, I could only mark one solution as the one.