Forum Discussion
Time formatting in Excel
I'm trying to do a dashboard 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 / formu...
Thanks JKPieterse and @Peter_Bartholomew. I've attached a two line sample of the challenge. I can't ignore the date aspect because it does interfere with the MM:SS format when I try and analyse it in Tableau. The formula given I can't make work.
If you can look at the attached for 2 mins I'd be grateful.
I see the problem. Because the time is more than 24 hrs, Excel adds the strange date part in the formula bar. Perhaps you can avoid the problem by formatting the time column as decimal number and then use tabloid to do the time format? Like the others I don't know tableau.
I've followed all of the tips and the MOD formula works to change the number into the MM:SS format but when I then cut and paste it into another column, it reverts back to the 1/1/1900 MM:SS:00 format - I'm really puzzled!
- In Excel, dates are expressed as a number of days since a starting date. Day 1 is Jan 1, 1900. Time is held as a fraction of a day, so 0.5 equals noon, 1 equals midnight. Your times are more than 24 hours, so you get numbers larger than 1.0, which -in the formula bar- Excel translates to a combination of date and time, even though it shows up in the cell formatted as hours:minutes.