Display minutes and seconds in a cell

Posted on 2011-10-30
Last Modified: 2012-05-12
Is there a way of displaying a cell value for times so that minutes and seconds can be displayed?

I.e. I can display a time 39.2 seconds and it's easy to do comparisons based on this.

But how to display 5 minutes 40.4 seconds without converting to 340.4 seconds.

It would be nice to have it as 5m40.4 or something like that.
Question by:tonydav67
    LVL 50

    Accepted Solution


    You can enter time values like this


    to show 5 minutes, 40 seconds and 4 milliseconds. Time values like that can be used in calculations and comparisons.

    You can format the cell with custom number format


    to display as 5m40.4

    cheers, teylyn

    LVL 50

    Expert Comment

    If you enter the values like this:


    to display 39 seconds and 2 milliseconds, you can convert the value into a time value by dividing by 86400


    then use the custom format like in the post above.
    LVL 50

    Expert Comment

    by:barry houdini
    Hello tonyday67,

    If you type 5:40.4 into a cell the excel will display it that way and knows it that represents 5 mins 40.4 seconds. [Note if you type that without the decimal place, i.e. 5:40 then excel treats that as 5 hours 40 mins so you need to include zero hours in that case, i.e. 0:05:40]

    If you have 5:40.4 in a cell then if you calculate with that value you might need to convert to 340.4 in which case you simply multiply by 86400 (the number of seconds in a day)

    regards, barry
    LVL 2

    Author Closing Comment

    Thank you very much. Exactly what I wanted. The calculations are quite simple (i.e. greater or less than) so this does the job without needing to go any further.

    Thanks everyone else for your additional assistance.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now