=TEXT(A1,"mm") not working

supermom28115

New Member
Joined
May 9, 2022
Messages
43
Office Version
  1. 365
Platform
  1. Windows
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

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

What am I doing wrong?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
You're trying to concat text and a number.

Does this work?
Excel Formula:
=TEXT(A2,"mm")&TEXT(C2,"00")
 
Upvote 0

Attachments

  • Screenshot 2023-10-03 191248.png
    Screenshot 2023-10-03 191248.png
    14.1 KB · Views: 17
Upvote 0
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
ABC
1DatePROD#Event#
29/16/23090202
399/22/202399/22/20230303
Sheet2
Cell Formulas
RangeFormula
B2:B3B2=TEXT([@Date],"mm")&TEXT([@[Event'#]],"00")
C2:C3C2=ROW(A2)
 
Upvote 0
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
ABC
1DatePROD#Event#
29/16/23090202
399/22/202399/22/20230303
Sheet2
Cell Formulas
RangeFormula
B2:B3B2=TEXT([@Date],"mm")&TEXT([@[Event'#]],"00")
C2:C3C2=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 .
 
Upvote 0
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
ABC
1DatePROD#Event#
29/16/23090202
399/22/202399/22/20230303
Sheet2
Cell Formulas
RangeFormula
B2:B3B2=TEXT([@Date],"mm")&TEXT([@[Event'#]],"00")
C2:C3C2=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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top