• Status: Solved
• Priority: Medium
• Security: Public
• Views: 240

# 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
• 2
1 Solution

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

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

Commented:
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

Author 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.