month datevalue language issue

[davidov]

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)?

 

[steve the fish]

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)

 

[davidov]

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]

As Steve the Fish advised, use your word for January and September in the example below

Excel 2010
	A	B	C
1		Date	End of Month
2	January	1-Jan-18	31-Jan-18
3	September	1-Sep-18	30-Sep-18
4
2c

Cell Formulas
Range	Formula
B2	=0+(1&A2)
B3	=0+(1&A3)
C2	=EOMONTH(B2,0)
C3	=EOMONTH(B3,0)

 

[davidov]

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

[TABLE="width: 964"]
<tbody>[TR]
[TD="width: 964"][TABLE="width: 500"]
<tbody>[TR]
[TD]Formula[/TD]
[/TR]
[TR]
[TD][TABLE="width: 964"]
<tbody>[TR]
[TD="width: 964"]=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)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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.[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

try
=IF(A1="","",EOMONTH(0+(1&A1),0+(MONTH(0+(1&A1)) < MONTH(TODAY()))*12))

Excel 2010
	A	B	C
1	January		31-Jan-19
2c

Cell Formulas
Range	Formula
C1	=IF(A1="","",EOMONTH(0+(1&A1),0+(MONTH(0+(1&A1))TODAY()))*12))