5 Replies Latest reply on Sep 18, 2018 2:20 PM by Hari Ankem

    How to format date with only Day of Week and Time?

    Heidi Kiser

      Hello, I'm using v18.1 and have been using Tableau for about 9 months. Every now and then, I get completely tripped up in calculated fields, and I appreciate your help!

       

      This is what my data looks like. (The packaged workbook is attached as well.)

       

      I've managed to dateparse columns F and G into [Dep Time] and [Arr Time]. I've also dateparsed column J into 7 fields [DayOp1] if it contains a 1, [DayOp2] through [DayOp7] for the seven weekdays.

       

      I need this to become Day Op 1 @ 9:25 for each of the months of flights in columns K, L and M and have tried to use the makedate function, but am unsuccessful.

       

      For my purposes, it doesn't matter which date in the months of June/July/August, just that I can compare the sum of activity on Day 1 to other 6 weekdays in each month at the same time +/- 5 minutes.  As you can see, some flight combinations are on all 7 weekdays, while others are only on 2 & 4 or 3, etc (column J), so they will not all be the same.

       

      Thank you in advance!!

       

      Heidi

        • 1. Re: How to format date with only Day of Week and Time?
          Hari Ankem

          Does this look right to you?

          1.png

           

          To achieve the above results, I copied your data into an Excel sheet and then did a pivot to get the Year-Month into a single column followed by creation of multiple calculated fields as shown below:

          1.png

          1.png 

          1.png

           

          1.png 1.png

           

          Hope this helps. Let me know if you didn't understand something in the above formulas. The updated workbook and the data file I have used are included.

          • 2. Re: How to format date with only Day of Week and Time?
            Heidi Kiser

            Yes, this is the output I was looking for!  It was really more of a data setup issue to get the month and year into the data.

             

            Can you explain to me why the [Dep Time] = DATEPARSE ("kkmm",[Local Dep Time])  did not work? I had changed the format of [Local Dep Time] to a string first. I might try to do this in the future if I don't understand why it doesn't work.

             

            Unfortunately, I need more than just the LEFT(Total Days of Op],1). I need the data for each day of the week present in that string, 1-7, so if all seven digits are there, then I have to identify that (CONTAINS([Local Days of Op],1) for each of the seven digits. Once I setup the workbook, I'm duplicating it for a total of seven workbooks. In just Day1, with the data mutipled x 3 for three months of data, I have 575,000 rows in the full data set. Now I'll multiply that by 7 and record the total output as one dataset, so I'm having to setup an Access Db.

             

            Thank you for your help!

             

            Heidi

            • 3. Re: How to format date with only Day of Week and Time?
              Hari Ankem

              The reason why your DATEPARSE function didn't work is because the date format symbol "k" that you have used is not valid. Refer this for the valid symbols: Custom Date Formats.

               

              Anyway, if you think you can take it forward from here and do not have any other questions, please close this thread.

              • 4. Re: How to format date with only Day of Week and Time?
                Heidi Kiser

                Hari,

                 

                You answered my original question so well, I want to request if you would answer a follow-on question on the same Workbook.

                 

                Previously, I said, "it doesn't matter which date in the months of June/July/August", so we calculated the date only within the first week of each month.

                 

                Now now I have an [EFFECTIVE FROM] and [EFFECTIVE TO] date, so when a [WEEKDAY] = 1, I need to return a result in all weeks in which weekday 1 falls in those dates. For instance, in workbook Day1-Dept, notice the Nulls that are coming back in DepartureDtTim1 and Weekday for the latter weeks of June. This is because in Date1 limits the response to the first week of June.

                 

                Is it possible to add to this formula to provide the same result for not just June 4th but also June 11,18 and 25 without creating a separate Calculated field for every day in the month? I already have 7 of everything for DeptDaysofOp 1-7.

                 

                IF DATE(

                    DATEADD("day",

                    7-DATEPART("weekday",DATEPARSE("yyyyMM",[Year-Month]))

                                +[DayOp1-Dept],

                    DATEPARSE("yyyyMM",[Year-Month])

                            )

                    )

                >= [EffectiveFrom]

                 

                 

                AND

                 

                 

                DATE(

                    DATEADD("day",

                    7-DATEPART("weekday",DATEPARSE("yyyyMM",[Year-Month]))

                                +[DayOp1-Dept],

                    DATEPARSE("yyyyMM",[Year-Month])

                            )

                    )

                <= [EffectiveTo]

                 

                 

                THEN DATE(

                    DATEADD("day",

                    7-DATEPART("weekday",DATEPARSE("yyyyMM",[Year-Month]))

                                +[DayOp1-Dept],

                    DATEPARSE("yyyyMM",[Year-Month])

                            )

                    )

                ELSE NULL

                END

                 

                Thank you so much! Heidi

                • 5. Re: How to format date with only Day of Week and Time?
                  Hari Ankem

                  You don't need to create a separate calculated field to get the other dates. But you need to do a calculated join of your current data with another table/file that contains a column of numbers from 0 to N, using the condition 1=1.  N can be any number depending on how many dates you need between the effective date range.