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
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.
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.
Ouch - slow - you've hurt an old man - add the excel file to the post and I will convert it for you
1 of 1 people found this helpful
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:
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.
I've attached a v2018.3 workbook with the sample data & the views above.
Hope this helps!
Had some issue with time part, might this help me.