Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

Display minutes and seconds in a cell

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.
  • 2
1 Solution
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

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

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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.
barry houdiniCommented:
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
tonydav67Author Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now