• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • 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.
0
tonydav67
Asked:
tonydav67
  • 2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

You can enter time values like this

5:40.4

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

m\mss.0

to display as 5m40.4

cheers, teylyn

0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
If you enter the values like this:

39.2

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

=A1/86400

then use the custom format like in the post above.
0
 
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
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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