4 Replies Latest reply on Oct 6, 2017 11:54 AM by Sean Morris

    Trying to convert date to YYYYMMDD, but no custom formatting option?

    Sean Morris

      Hi all. I have a date field that, in its original form, displays date as M/D/YYYY, and I need it formatted as YYYYMMDD (always 8 digits, 09011972 for example).

       

      Ideally I'd like to do this in the actual data source, but I don't think I can do that. So, to do this in a worksheet, I understand that I'm to bring in my field, right click, select "Format" and under dates, choose "custom."

       

      Only, I do not see the option to create a custom date format. "Automatic" is the only option available.

        • 1. Re: Trying to convert date to YYYYMMDD, but no custom formatting option?
          Jim Dehner

          Hi

          Have you followed the path shown below for get to date format

           

           

          then a box  opens and custom is on the bottom (may need to scroll)

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Trying to convert date to YYYYMMDD, but no custom formatting option?
            Sean Morris

            Ok, so I am able to get to this window, and I enter YYYYMMDD in that Format field, but when I bring it into my worksheet, it just displays the year. Is there another step I need to take to get the field to display the way I want it to?

            • 3. Re: Trying to convert date to YYYYMMDD, but no custom formatting option?
              Jim Dehner

              OK took a little more

               

              see below

              I created a new date field using the formula below - it looks forboding but just put your put your date field on columns and set to MDY then drage it onto the Create Calculation canvas

               

              Place it in the viz - you may need to convert to discrete and also change the format to date

               

               

              Jim

               

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              • 4. Re: Trying to convert date to YYYYMMDD, but no custom formatting option?
                Sean Morris

                Ok, so I was having trouble with this too. Calculation was valid but field was STILL just being shown as a year when I brought it into my worksheet.

                 

                So, here's what I did to finally crack the code.

                 

                In my worksheet view, I hovered over the date dimension pill on the left hand side, and clicked the little down arrow to the right. From there, I selected "Default Properties" and then "Date Format," making sure that "Date" was selected under "Data Type."

                 

                In the date format window, I scrolled down and selected "Custom" and in the empty field to the right, typed in YYYYMMDD (no quotes).

                 

                This changed how the date appeared in my actual data source, which is what I wanted.

                 

                Now, when I tried to bring in this formatted date into my worksheet, it STILL defaulted to showing only the year. To change this, I had to right click the date pill in my rows shelf and select "Exact date."

                 

                Doing this automatically converts the variable to continuous and tries to make the table display some sort of bar chart. To just get a list of the formatted dates, I had to right click the pill on the rows shelf again and select "Discrete."

                 

                Woof. This process needs to be streamlined.