=TEXT(A1,"mm") not working

[supermom28115]

Attempting to create the first four digits of an item code for a product log using =text(a1, "mm") where a1 has transaction date 9/16/2023 but I only want the month in a two digit format with a two digit autonumber from another cell using the =Row(A1) with a custom number format of 00. I have tried using concat and the =text(a1,"mm") with & but it just will not give me a two digit month only. I end up with 9/16/202301

DATE	PROD #	AUTONUMBER
9/16/2023		=ROW(A1) CUSTOM FORMAT 00
9/22/2023		=ROW(A2) " "

What am I doing wrong?

 

[BigBeachBananas]

You're trying to concat text and a number.

Does this work?

=TEXT(A2,"mm")&TEXT(C2,"00")

 

[supermom28115]

You're trying to concat text and a number.

Does this work?

=TEXT(A2,"mm")&TEXT(C2,"00")

here is the screenshot

 

[BigBeachBananas]

Mine works for the first row. Since 99 is not a real month it will do that. What data type do you have for the first and third columns (not the format). If possible, can you share your sheet using XL2BB

Book1
	A	B	C
1	Date	PROD#	Event#
2	9/16/23	0902	02
3	99/22/2023	99/22/202303	03
Sheet2

Cell Formulas
Range	Formula
B2:B3	B2	=TEXT([@Date],"mm")&TEXT([@[Event'#]],"00")
C2:C3	C2	=ROW(A2)

 

[supermom28115]

Mine works for the first row. Since 99 is not a real month it will do that. What data type do you have for the first and third columns (not the format). If possible, can you share your sheet using XL2BB

Book1
	A	B	C
1	Date	PROD#	Event#
2	9/16/23	0902	02
3	99/22/2023	99/22/202303	03
Sheet2

Cell Formulas
Range	Formula
B2:B3	B2	=TEXT([@Date],"mm")&TEXT([@[Event'#]],"00")
C2:C3	C2	=ROW(A2)

the 99 was a typing error, and as far as data type, other than formatting the cell as a date I haven't set a data type that I know of. I know data types are used in VBA but I was trying to avoid using VBA. I am not sure what you are doing differently than I what I have done. I can't find any reference to data type to change those cells .

 

[supermom28115]

Mine works for the first row. Since 99 is not a real month it will do that. What data type do you have for the first and third columns (not the format). If possible, can you share your sheet using XL2BB

Book1
	A	B	C
1	Date	PROD#	Event#
2	9/16/23	0902	02
3	99/22/2023	99/22/202303	03
Sheet2

Cell Formulas
Range	Formula
B2:B3	B2	=TEXT([@Date],"mm")&TEXT([@[Event'#]],"00")
C2:C3	C2	=ROW(A2)

I tried it on a new sheet and still can't duplicate what you have done. I can't even get my date to auto align right. Now I am just getting frustrated with trying to even work with excel. The only data types I see in my tool bar are stocks, geography, and currency.

 

[BigBeachBananas]

I tried it on a new sheet and still can't duplicate what you have done. I can't even get my date to auto align right. Now I am just getting frustrated with trying to even work with excel. The only data types I see in my tool bar are stocks, geography, and currency.

Is it possible for you to share a sample of your workbook via DropBox?

 

[supermom28115]

Is it possible for you to share a sample of your workbook via DropBox?

Bookkeeping test.xlsm

Shared with Dropbox

www.dropbox.com

 

[BigBeachBananas]

Bookkeeping test.xlsm

Shared with Dropbox

www.dropbox.com

 

[MARK858]

I can't even get my date to auto align right

What date format are your dates normally in dd/mm/yyyy or mm/dd/yyyy?