To display mm:ss format in Excel

heyday2004
heyday2004 used Ask the Experts™
on
I have an excel file, with a simple pivot table. I have a field with the data stored as number of seconds, say 91 second.  How could I change the excel file cell setting so it will be displayed in mm:ss format, like 01:31? Any quick way to do that? Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I think you cannot do this directly. You will have to convert the number to a time by dividing by (24*60*60) and then formatting the numbers accordingly.
Hi, heyday2004.

A perfect use for pivot table's Calculated Field?

Edit:... as long as you don't need it as a Row/Column Label.

Regards,
Brian.
Commented:
If it is possible, you can change the stored content as follow :

instead of 91 seconds, you store 91/(24*60*60)
Then you can apply the custom format "mm:ss" on the cell.
 This will display 01:31


sorry, this is the same answer than the first one...

Author

Commented:
many thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial