# Number as date - formula for different regional settings

• ### Question

• Hi good people

Formula to present number as date drives me crazy. No VBA is required in this case.

In column C following numbers are entered.
C9    201501
C10  201502
C11  201503 etc.

In column "D" I need to define last day of respective month and year
For example: 201502 should be presented as 28-Feb-2015
Formula =EOMONTH(C9,0) works only if in the cell A9 actual date is entered. In my case it is the number, therfore it returnes incorrect date as 30-Sep-51.
In order to present number 201501 as date I used following formula

=DATEVALUE("01/"&IF(MID(C9,5,1)=0,RIGHT(C9,1),RIGHT(C9,2))&"/"&LEFT(C9,4)) were result is 1-Jan-15
Accordingly final formula is:
=EOMONTH(DATEVALUE("01/"&IF(MID(C9,5,1)=0,RIGHT(C9,1),RIGHT(C9,2))&"/"&LEFT(C9,4)),0)it returnes 31-Jan-15
If you drag formula - result for 201502 in cell D10 instead of 28-Feb-15 once again will be displayed as 31-Jan-15 .

On my laptop date format is set to US (MM-DDD-YY), It will work with PC's where date format is set as ("DD-MMM-YY")

Sure I can swap "month" with "date" in the formula, or change regional settings alternatively, but it will be shared and will not work everywhere

Is it possible to modify formula the way it will meet different regional settings? Any other alternative to the formula?

Thursday, September 3, 2015 6:51 PM

• The formula

=DATE(INT(C9/100),MOD(C9,100)+1,0)

should be independent of the user's date format.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

• Marked as answer by Thursday, September 3, 2015 8:18 PM
Thursday, September 3, 2015 7:04 PM

### All replies

• The formula

=DATE(INT(C9/100),MOD(C9,100)+1,0)

should be independent of the user's date format.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

• Marked as answer by Thursday, September 3, 2015 8:18 PM
Thursday, September 3, 2015 7:04 PM
• I have no words, so simple and the same time SMART (Genius)

Thanks a LOT and regards

Sincerely appreciated
• Edited by Thursday, September 3, 2015 8:59 PM
Thursday, September 3, 2015 8:21 PM