How to convert date from a hyphen "-" to an underscore "_"

[omairhe]

How do I convert a formula that outputs month number and year joined together with each other using a hyphen and change that into underscore. The formula I have is important because when last month of the year is reached, the plus 1 then gives me next year's date e.g. 01-2022

A3 = TEXT(DATE(A2,A1+1,1),"mm-yyyy")

A1 = 01
A2 = 2021

Current output = 01-2021

Desired output = 01_2021

Thanks.

 

[sandy666]

maybe

Book1
	A
1	01
2	2021
3	01_2021
Sheet1

Cell Formulas
Range	Formula
A3	A3	=A1&"_"&A2

 

[RossTattersall]

The above response will be fine for display purposes but won't actually be date.

Instead try;

A3= TEXT(DATE(A2,A1+1,1),"mm\_yyyy")

Without the slash Excel turns _ into a space in text formats for some reason (despite spaces being completely acceptable in formats!)

 

[Toadstool]

Hi Omairhe,

Try =TEXT(DATE(A2,A1+1,1),"mm\_yyyy")

 

[omairhe]

Bravo!!

 

[Dave Patton]

It may depend on if you require text or a date.

T202101a.xlsm
	A	B	C	D
1	01		Format
2	2021		General	7-Jan-21
3	01_2021	=A1&"_"&A2	01_2021	TRUE
4	01_2021	Text	01_2021	TRUE
5	01_2021	Date custom format as mm\_yyyy	44197	FALSE
6
4d

Cell Formulas
Range	Formula
B3	B3	=Gf(A3)
C3:C5	C3	=A3
D3:D5	D3	=A3>$D$2
A3	A3	=A1&"_"&A2
A4	A4	=TEXT(DATE(A2,A1,1),"mm\_yyyy")
A5	A5	=DATE(A2,A1,1)