Formatting in a formula

[Domroy]

Hi there,

I have a formula that is pulling from other formulas to create a unique number, and the format is coming out incorrectly:

To begin with, I have a date in cell K16. Down in S35, S36 and S37 respectively, I have it pulling the month =month(k16), day =text(day(k16),"dd") and Year =year(k16). Then, I have another formula pulling from those numbers, and I want the numbers to pull in as YYYYMMDD-22937. I have that pulling into the formula by doing =S37&S35&S36&"-"&K8 (where K8 has the 22937). The problem is that it's not formatting the month as two digits. And when I pull the month out as =TEXT(month(K16),"mm") - for all the months below 10, it calls them "01." But for my serial number, it needs to be two digits. So, for example, my date for today would pull out and show as YYYYMDD-22937 - instead of having it show as YYYYMMDD-22937 - with the TWO digit month.

Can anyone help me with this? Also, I've tried formatting the cell as two digits, but, as we all know, that does nothing for when it pulls into the formula. THANK YOU!

 

[Domroy]

Hi there,

I have a formula that is pulling from other formulas to create a unique number, and the format is coming out incorrectly:

To begin with, I have a date in cell K16. Down in S35, S36 and S37 respectively, I have it pulling the month =month(k16), day =text(day(k16),"dd") and Year =year(k16). Then, I have another formula pulling from those numbers, and I want the numbers to pull in as YYYYMMDD-22937. I have that pulling into the formula by doing =S37&S35&S36&"-"&K8 (where K8 has the 22937). The problem is that it's not formatting the month as two digits. And when I pull the month out as =TEXT(month(K16),"mm") - for all the months below 10, it calls them "01." But for my serial number, it needs to be two digits. So, for example, my date for today would pull out and show as YYYYMDD-22937 - instead of having it show as YYYYMMDD-22937 - with the TWO digit month.

Can anyone help me with this? Also, I've tried formatting the cell as two digits, but, as we all know, that does nothing for when it pulls into the formula. THANK YOU!

Ugh! One extra Google Search answered my question. It's =text(k16,"mm")

 

[jtakw]

Hi,

Use

=TEXT(MONTH(K16),"00")

What you posted won't work. (I mean in your OP)

 

[jtakw]

Also, You don't have to extract the different parts of the K16 Date into S35, S36, S37, unless you want to for some reason.
You can just do the entire conversion with 1 single cell formula:

Book3.xlsx
	A	B	C	D
1	3/9/2022	22937		20220309-22937
Sheet1047

Cell Formulas
Range	Formula
D1	D1	=TEXT(A1,"yyyymmdd")&"-"&B1