Forum Discussion
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 from what I can see.
- I also tried using =TEXT(A2,"DDDD") and it returned #VALUE. I pulled the file as a CSV but saved it as an excel workbook (not sure if that matters). Any help please?
Thank you,
Brandon
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.
7 Replies
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")
- Thank you Subodh, this worked
You're welcome 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 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
