Answered by:
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 28Feb2015
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 30Sep51.
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 1Jan15
Your amazing, smart answer is highly appreciated
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 31Jan15
If you drag formula  result for 201502 in cell D10 instead of 28Feb15 once again will be displayed as 31Jan15 .
On my laptop date format is set to US (MMDDDYY), It will work with PC's where date format is set as ("DDMMMYY")
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?