Forum Discussion

CHIZLETT123's avatar
CHIZLETT123
Copper Contributor
May 21, 2019

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...
excel
Formulas and Functions
JKPieterse's avatar
JKPieterse
Silver Contributor
May 21, 2019
I would just ignore the date part the formula bar displays, it should not affect your results unless you are doing date calculations as well.
  • CHIZLETT123's avatar
    CHIZLETT123
    Copper Contributor
    May 21, 2019

    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.

     

     

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      May 22, 2019
      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.
      • CHIZLETT123's avatar
        CHIZLETT123
        Copper Contributor
        May 23, 2019

        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! 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 21, 2019

      CHIZLETT123 ,

       

      Afraid that question is more for Tableau community. Apply General format to the time, you'll see the number. In Excel date is integer part of the number starting from 01 Jan 1900 (=1), and time is decimal part of the number. Thus dates are always will be added if your number is the equivalent of more than 24 hours.

Resources