Forum Discussion
Long date format returns ####
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.
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 I use this formula often, and would like to create a custom formula as this uses only one parameter (the date in yyyymmdd). I would like to write this as a custom formula to share with coworkers. Does the custom formula have to be manually written in VBA, or can I record it as a macro? (I have no VBA experience)
- Thank you Sergei, this worked
Brandon, you are welcome
