month datevalue language issue

davidov

New Member
Joined
Aug 1, 2017
Messages
10
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)?
 

Some videos you may like

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
Joined
Oct 20, 2009
Messages
8,112
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 1, 2017
Messages
10
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
Joined
Feb 15, 2002
Messages
4,254
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
As Steve the Fish advised, use your word for January and September in the example below

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Date</td><td style=";">End of Month</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">January</td><td style="text-align: right;;">1-Jan-18</td><td style="text-align: right;;">31-Jan-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">September</td><td style="text-align: right;;">1-Sep-18</td><td style="text-align: right;;">30-Sep-18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">2c</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=0+(<font color="Blue">1&A2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=EOMONTH(<font color="Blue">B2,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=0+(<font color="Blue">1&A3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=EOMONTH(<font color="Blue">B3,0</font>)</td></tr></tbody></table></td></tr></table><br />
 

davidov

New Member
Joined
Aug 1, 2017
Messages
10
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
Joined
Feb 15, 2002
Messages
4,254
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
try
=IF(A1="","",EOMONTH(0+(1&A1),0+(MONTH(0+(1&A1)) < MONTH(TODAY()))*12))

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">January</td><td style="text-align: right;;"></td><td style="text-align: right;;">31-Jan-19</td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">2c</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=IF(<font color="Blue">A1="","",EOMONTH(<font color="Red">0+(<font color="Green">1&A1</font>),0+(<font color="Green">MONTH(<font color="Purple">0+(<font color="Teal">1&A1</font>)</font>)<MONTH(<font color="Purple">TODAY(<font color="Teal"></font>)</font>)</font>)*12</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,503
Messages
5,529,248
Members
409,858
Latest member
mr20201
Top