none
Number as date - formula for different regional settings RRS feed

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

     Your amazing, smart answer is highly appreciated

    Thursday, September 3, 2015 6:51 PM

Answers

  • 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 GioPir 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 GioPir 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 GioPir Thursday, September 3, 2015 8:59 PM
    Thursday, September 3, 2015 8:21 PM