# month datevalue language issue

#### davidov

##### New Member
Hi all,

I have a column with month names in English (January, February, etc) and I wanted to transform them into months (and in the end in a complete date based on month-end) with this formula:
=MONTH(DATEVALUE(C2&" 1"))

However my regional language is not in English, but the months are. So for most months I get the #value error.

So does anyone know how to solve this?

And bonus question any way to transform just the month-name with a formula to 1-month-first year month-name happens again.
So it's September now, so if it is still September it would be 1-9-2018, no matter which day in September it actually is. However if month-name would be July it would have to say 1-7-2019. Is this possible (without VBA)?

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### steve the fish

##### Well-known Member
If your version doesnt understand January then id use either whatever word you need for it to understand or some sort of lookup table to do a conversion. Be careful as if i type in January 1 in my machine it will think i mean 1st January 2001.

For the second part id do:

=DATE(YEAR(TODAY()),MONTH(1&A1),1)

With 'January' in A1. That may also be location specific though.

Edit:

You potentially can just do:

=0+(1&A1)

Last edited:

#### davidov

##### New Member
Lookup table of course. I was thinking too much in my little box. Thank you, and I am going to experiment with part 2

#### Dave Patton

##### Well-known Member
As Steve the Fish advised, use your word for January and September in the example below

#### davidov

##### New Member
Yes thank you as well. I made a nice formula in the end

Formula
 =IF(A1="","",IF(EOMONTH(DATE(YEAR(TODAY()),MONTH(1&A1),1),0)>TODAY(),EOMONTH(DATE(YEAR(TODAY()),MONTH(1&A1),1),0),EOMONTH(DATE(YEAR(TODAY())+1,MONTH(1&A1),1),0)))

<tbody>
</tbody>

<tbody>
</tbody>

If the cell is blank then blank, if today is in the given month or still has to come the year will be 2018. If the month is in the past, the year will be 2019 (2018+1) instead of just 2018.

<tbody>
</tbody>

#### Dave Patton

##### Well-known Member
try
=IF(A1="","",EOMONTH(0+(1&A1),0+(MONTH(0+(1&A1)) < MONTH(TODAY()))*12))

Last edited:

Replies
7
Views
51
Replies
7
Views
115
Replies
2
Views
30
Replies
0
Views
58
Replies
3
Views
60