5 Replies Latest reply on May 29, 2019 10:08 AM by Kishor c

    Time formatting in Excel

    Pete Chizlett

      I'm brand new to Tableau and am trying to do a viz using an export of data from a 10km race. The Chip time is shown in minutes (elapsed) but when I click in the cell in Excel is shows a completely different format / formula (I'm not sure which).

       

      When I bring the file into Tableau is shows as a date in the format above the chip time but I'd like to be able to show and analyse the chip time in MM:SS format.

       

        • 1. Re: Time formatting in Excel
          Jim Dehner

          Good morning Pete

          first congrats on the 10K   nice time

           

          the easiest solution I have found in similar situations is to create the fix on the excel side by creating a date field in inserting the race date and then adding the datevalue + timevalue   in excel to create tha date/time field

          then bring that into tableau as a datetime field

           

           

          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: Time formatting in Excel
            Pete Chizlett

            Thanks Jim - my time was a little slower to be honest

             

            I've tried to fix this on the Excel side and am struggling to do so. The date shown is obviously not current. I think it might be a format used in SQL so I wondered if anyone had seen this type of thing. It displays as the MM:SS format in Excel but shows as this wierd date format when I click on the Excel cell.

            • 3. Re: Time formatting in Excel
              Jim Dehner

              Ouch - slow - you've hurt an old man -  add the excel file to the post and I will convert it for you

              Jim

              • 4. Re: Time formatting in Excel
                Jonathan Drummey

                Hi Pete,

                 

                I've spent a lot of time (pun not intended) with Excel & Tableau data & time formatting and I think I can explain what is going on here.

                 

                In Excel (and Tableau, and many databases) *date storage* is different from *date formatting*. In this case we're seeing a race time of 33:33:00 which would be stored as 1.398 (roughly). If you change the format of the date in Excel to a number with decimal places you should see that.

                 

                The 1.398 is the number of decimal days from 0 January 1900, only Excel skips the leap year in 1900 (since Lotus 1-2-3 did that as well). See  Re: Date calc to integer is not correct for a dive into why 0 January 1900.

                 

                So the 1.398 is effectively added to 30 December 1899 and when we add 1.398 days to that we get 1 January 1900 and .398 days gets us to 9:33:00 am. That explains why you're seeing the 1/1/1900 09:33 in the format window.

                 

                Now you might be wondering why the 33:33:00 got converted to 1.398 in the first place. I'm pretty sure that the race time was initially entered as 33:33:00 and when entered (or opened in Excel) instead of interpreting this as 33 minutes:33 seconds: 0 hundredths Excel automatically converted the entered data to 33 hours: 33 minutes: 0 seconds, and that turns into 1 day, 9 hours, and 33 minutes, which expressed as a decimal turns into 1.398.

                 

                Therefore the underlying number in Excel is accurate, but effectively in a different "language". We need to translate that back to something. I mocked up some data and did this in Tableau because that's my habit (I get lots of Excel files that are updated regularly so I like to do as little hand-editing as possible and can just hit refresh in my Tableau viz).

                 

                Here's a view:

                 

                Screen Shot 2019-05-21 at 11.13.04 AM.png

                 

                The Tableau Hour/Minute/Seconds calcs  are using the DATEPART() function to extract those components from the original time. Then the Tableau Actual Minutes/Seconds takes those numbers and converts them into the actual minutes and seconds that we would expect to see. However we really want one number to work with so we need to go a little further. The Tableau Race Time as Seconds converts those into the # of seconds of elapsed time (so multiplying the Tableau Actual Minutes that is actually the minutes * 60 seconds per minute and so on).

                 

                The Tableau Race Time as Decimal Days then converts the seconds into days by dividing the seconds by 86400, which is the number of seconds in a day.

                 

                Then finally the Tableau Race time is using the same formula with a default number formatting of nn:ss.00 which gets us the minutes:seconds:hundredths. Note that this works when the elapsed time is less than 24 hours, see Formatting Time Durations in Tableau | Drawing with Numbers for more details.

                 

                One place this would all fall down is if a racer had a time that was greater than 1 hour. Entering 1:05:28:00 in Excel causes that cell to be recognized as text and then Tableau will take in the entire column as text.

                 

                The workaround for that is to use Tableau's DATEPARSE() function to convert the strings into usable times and then similar number formatting on the results.

                 

                Screen Shot 2019-05-21 at 11.23.41 AM.png

                 

                I've attached a v2018.3 workbook with the sample data & the views above.

                 

                Hope this helps!

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: Time formatting in Excel
                  Kishor  c

                  Had some issue with time part, might this help me.

                   

                  Thanks Jonathan