how to convert text month into real date

[mart_mrexcel]

I have a column with month name as text (3 letters). e.g Jan, Feb, Mar. How can i convert it to real date (1st day of the month of the current year).

Jan - should be 01-Jan-2022

Thanks

 

[Peter_SSs]

How do you figure current year is 2022?

Is it really this?

21 06 09.xlsm
	A	B
1
2	Jan	01-Jan-2021
3	Feb	01-Feb-2021
4	Mar	01-Mar-2021
5	Apr	01-Apr-2021
6	May	01-May-2021
7	Jun	01-Jun-2021
8	Jul	01-Jul-2021
9	Aug	01-Aug-2021
10	Sep	01-Sep-2021
Date

Cell Formulas
Range	Formula
B2:B10	B2	=DATEVALUE(1&A2)

 

[mart_mrexcel]

How do you figure current year is 2022?

Is it really this?

21 06 09.xlsm
	A	B
1
2	Jan	01-Jan-2021
3	Feb	01-Feb-2021
4	Mar	01-Mar-2021
5	Apr	01-Apr-2021
6	May	01-May-2021
7	Jun	01-Jun-2021
8	Jul	01-Jul-2021
9	Aug	01-Aug-2021
10	Sep	01-Sep-2021
Date

Cell Formulas
Range	Formula
B2:B10	B2	=DATEVALUE(1&A2)

im sorry should be year 2021..... but in any case if i have cell value with a year 2022 (e.g. A1 i have 01-Jan-2022) and and i want to use that year to your formula =DATEVALUE(1&A2)?

 

[Peter_SSs]

e.g. A1 i have 01-Jan-2022) and and i want to use that year to your formula =DATEVALUE(1&A2)?

Is this a different question? You said originally that you just had a 3 letter month name to convert.
Can you clarify?

 

[mart_mrexcel]

Is this a different question? You said originally that you just had a 3 letter month name to convert.
Can you clarify?

The same question...what i mean is that i want to use the Year in 01-Jan-2022 to covert (e.g.) text Feb into 01-Feb-2022

 

[Peter_SSs]

i want to use the Year in 01-Jan-2022

What cell is this in?

text Feb

What cell is this in?

 

[mart_mrexcel]

What cell is this in?



What cell is this in?

A1

 

[Peter_SSs]

01-Jan-2022 is a date (or possible text) and Feb is a text value, but in any case is different to "01-Jan-2022". How can they both be in A1?

 

[mart_mrexcel]

01-Jan-2022 is a date (or possible text) and Feb is a text value, but in any case is different to "01-Jan-2022". How can they both be in A1?

Sorry to confuse you... Actually the 3 text month (Jan, Feb) in column is to be converted in a real date with First day of the month but depends on the year that is in a cell value. the formula you gave is perfectly ok. i just wonder what if i want to make it as year 2022, 2023 instead of 2021.

In your formula i add 1 in the year to make it year 2022 or 2 to make it 2023. I was wondering that instead of adding number in the year i want to use the year in a certain cell value (it can be placed anywhere)

=DATE(YEAR(DATEVALUE(1&A2)+1,MONTH(DATEVALUE(1&A2)),DAY(1))

 

[Peter_SSs]

i want to use the year in a certain cell value (it can be placed anywhere)

Thank you.
Is this what you mean?

21 06 09.xlsm
	A	B
1	01-Jan-2023
2	Jan	01-Jan-2023
3	Feb	01-Feb-2023
4	Mar	01-Mar-2023
5	Apr	01-Apr-2023
6	May	01-May-2023
7	Jun	01-Jun-2023
Date

Cell Formulas
Range	Formula
B2:B7	B2	=DATEVALUE(1&A2&YEAR(A$1))