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
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.

SergeiBaklan

Diamond Contributor

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.

Brandon123

Copper Contributor

Oct 16, 2019

Thank you Sergei, this worked

SergeiBaklan
Diamond Contributor
Oct 16, 2019
Brandon, you are welcome

Forum Discussion

Long date format returns ####

Resources