Forum Discussion

Brandon123's avatar
Brandon123
Copper Contributor
Oct 16, 2019
Solved

Long date format returns ####

I'm trying to convert yyyymmdd (20180904) to find the day of the week (Monday).      - Changing the format returns a very long #### with no end.      - There is nothing negative about this number f...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Oct 16, 2019

    Brandon123 

    Hi Brandon,

     

    Dates in Excel are actually sequential integers where 1 is equivalent of 01 Jan 1990, and ends by year 9999. In particular 04 Sep 2018 is equal to 43347. Your number 20180904 is much above the range, thus TEXT returns an error. 

    You shall transform you numbers to dates, as variant by formula like

    =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

    If your dates are in column, you may select it, Data->Text To Columns and on third step of the wizard select date and YMD.

Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
Oct 16, 2019

Brandon123 

Please give this a try and see if you get the desired output...

 

=TEXT(DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)),"dddd")

  • Brandon123's avatar
    Brandon123
    Copper Contributor
    Oct 16, 2019
    Thank you Subodh, this worked

Resources