Microsoft Office Forums Format cells to minutes and seconds

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-22-2019, 06:32 AM
RonDBD RonDBD is offline Format cells to minutes and seconds Windows 8 Format cells to minutes and seconds Office 2010 64bit
Novice
Format cells to minutes and seconds
 
Join Date: Oct 2013
Posts: 24
RonDBD is on a distinguished road
Default Format cells to minutes and seconds

I am tracking the songs on an album. There is a time per song in minutes and seconds. I want to format another column to contain a running sum of these times, in minutes and seconds. There is a predefined format for 13:30:55, but this requires the presence of hours in the length of a song. If I set the length of a song to 13:30:55 format with no hours present, it sums as hours and minutes.
Another way of phrasing the question ..is it possible to set a custom format for time?
Ron
Reply With Quote
  #2  
Old 09-22-2019, 09:04 AM
Logit Logit is offline Format cells to minutes and seconds Windows 10 Format cells to minutes and seconds Office 2007
Expert
 
Join Date: Jan 2017
Posts: 267
Logit is on a distinguished road
Default

While in the FORMAT screen, scroll down to the bottom and select CUSTOM.

I believe the format you will enter should be mm:ss
Reply With Quote
  #3  
Old 09-22-2019, 11:06 AM
RonDBD RonDBD is offline Format cells to minutes and seconds Windows 8 Format cells to minutes and seconds Office 2010 64bit
Novice
Format cells to minutes and seconds
 
Join Date: Oct 2013
Posts: 24
RonDBD is on a distinguished road
Default no joy

I just discovered Custom settings, and thought that was the answer. However, if I set a column to mm:ss it still interprets as hours and minutes. Go figure...
Reply With Quote
  #4  
Old 09-22-2019, 12:02 PM
Logit Logit is offline Format cells to minutes and seconds Windows 10 Format cells to minutes and seconds Office 2007
Expert
 
Join Date: Jan 2017
Posts: 267
Logit is on a distinguished road
Default

Something else must be going on with your workbook. Can you post it here for review ?
Reply With Quote
  #5  
Old 09-22-2019, 01:16 PM
RonDBD RonDBD is offline Format cells to minutes and seconds Windows 8 Format cells to minutes and seconds Office 2010 64bit
Novice
Format cells to minutes and seconds
 
Join Date: Oct 2013
Posts: 24
RonDBD is on a distinguished road
Default mm:ss format problem

See attached. Col C contains song length in mm:ss: it imported from a pdf that way.
I copied C to D and set Format Cells to mm:ss: now numbers get interpreted as hh:mm, for display. Format as shown in data entry area is hh:mm:ss AM.
Puzzling. I can solve the problem by adding 0: to the front of each length, because I then have h:mm:ss.
Attached Files
File Type: xlsx List 1 - Copy.xlsx (10.8 KB, 5 views)
Reply With Quote
  #6  
Old 09-22-2019, 02:31 PM
Logit Logit is offline Format cells to minutes and seconds Windows 10 Format cells to minutes and seconds Office 2007
Expert
 
Join Date: Jan 2017
Posts: 267
Logit is on a distinguished road
Default

What I've been able to determine.

As you indicated ... the times are being brought over from a PDF file. That format is very different from Excel's, requiring it be converted to something Excel understands / recognizes.

I tried several free online conversion sites with the data located in Col D. I also attempted a conversion with a macro in Excel. Did not have any success.

The times are currently seen by Excel as hours / minutes ... instead of minutes / seconds. No matter what FORMAT you use in Excel, without the proper conversion first from PDF to Excel .... you will most likely always receive an error.

I am unable to advise you what next step to take ... other than continue attempting to convert the original file from PDF to Excel first. Alternative is to manually enter the information.

Perhaps someone else will have an automated process.

Sorry.
Reply With Quote
  #7  
Old 09-22-2019, 02:34 PM
RonDBD RonDBD is offline Format cells to minutes and seconds Windows 8 Format cells to minutes and seconds Office 2010 64bit
Novice
Format cells to minutes and seconds
 
Join Date: Oct 2013
Posts: 24
RonDBD is on a distinguished road
Default Thanks!

Really appreciate your efforts. I never considered that the conversion from pdf to xls was the problem. I tried converting to Word, but that was a mess. I will just get used to adding a 0: to all of the times...
Ron
Reply With Quote
  #8  
Old 09-22-2019, 05:34 PM
NoSparks NoSparks is offline Format cells to minutes and seconds Windows 7 64bit Format cells to minutes and seconds Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 743
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

You can change the times directly in the C column using VBA,
but if you want a formula solution in column D, try this in D1 and drag down
= TIMEVALUE("00:" & HOUR(C1) & ":" & MINUTE(C1))
Reply With Quote
  #9  
Old 09-22-2019, 05:38 PM
RonDBD RonDBD is offline Format cells to minutes and seconds Windows 8 Format cells to minutes and seconds Office 2010 64bit
Novice
Format cells to minutes and seconds
 
Join Date: Oct 2013
Posts: 24
RonDBD is on a distinguished road
Default mm:ss format problem

Cool! I had looked at Timevalue but not that way. I'll try it out! Thx!
Reply With Quote
  #10  
Old 09-23-2019, 01:23 AM
ArviLaanemets ArviLaanemets is offline Format cells to minutes and seconds Windows 8 Format cells to minutes and seconds Office 2016
Expert
 
Join Date: May 2017
Posts: 488
ArviLaanemets has a spectacular aura aboutArviLaanemets has a spectacular aura about
Default

Another possible solution.


NB! I used the format [m]:ss, which allows minutes over 60 sec. displayed. And replaced PdfTime for last row from 2:25 to 52:25 to get sum > 60 minutes as example.
Attached Files
File Type: xlsx List 1 - Copy.xlsx (11.7 KB, 6 views)

Last edited by ArviLaanemets; 09-23-2019 at 04:54 AM.
Reply With Quote
  #11  
Old 09-23-2019, 02:18 PM
RonDBD RonDBD is offline Format cells to minutes and seconds Windows 8 Format cells to minutes and seconds Office 2010 64bit
Novice
Format cells to minutes and seconds
 
Join Date: Oct 2013
Posts: 24
RonDBD is on a distinguished road
Default

I see in your copy that you have the setting [m]:ss. However, that option is not present in my worksheets. Did you add it? How do you do that?
Reply With Quote
  #12  
Old 09-23-2019, 10:37 PM
ArviLaanemets ArviLaanemets is offline Format cells to minutes and seconds Windows 8 Format cells to minutes and seconds Office 2016
Expert
 
Join Date: May 2017
Posts: 488
ArviLaanemets has a spectacular aura aboutArviLaanemets has a spectacular aura about
Default

FormatCells>Custom: Into Type field enter the format.


You can also use formats like "[h]:mm:ss" or "[s]", but not "[d]" or "[d] hh:mm:ss".


The feature is available at least from year 2000, when I started to use MS Office instead of Quattro Pro and Visual FoxPro. But I'm not sure about web-based Office! I have MS Office installed in my computer.
Reply With Quote
  #13  
Old 09-24-2019, 04:51 AM
RonDBD RonDBD is offline Format cells to minutes and seconds Windows 8 Format cells to minutes and seconds Office 2010 64bit
Novice
Format cells to minutes and seconds
 
Join Date: Oct 2013
Posts: 24
RonDBD is on a distinguished road
Default

Well, color me stupid! I guess I interpreted General as a title and never thought to enter in a format! Works great: thanks again!
Reply With Quote
Reply

Tags
format cells

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Outlook 2013 Messages can sit in the Outbox anywhere from 5 seconds up to 60 seconds BNS1968 Outlook 0 09-16-2016 08:07 PM
Format cells to minutes and seconds Format a range of cells Dave T Excel Programming 1 06-01-2016 08:58 PM
Format cells to minutes and seconds Convert large seconds values to a time format fails with #Value Sekerob Excel 3 07-18-2015 01:56 PM
Format cells to minutes and seconds Procedure for displaying symbols for degrees, minutes, seconds kinglaird Excel 1 01-17-2015 02:04 AM
Format cells to minutes and seconds How-TO format cells (FILL) by comparing cells zanat0s Excel 1 07-03-2012 04:27 AM


All times are GMT -7. The time now is 02:54 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft