#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
While in the FORMAT screen, scroll down to the bottom and select CUSTOM.
I believe the format you will enter should be mm:ss |
#3
|
|||
|
|||
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...
|
#4
|
|||
|
|||
Something else must be going on with your workbook. Can you post it here for review ?
|
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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)) |
#9
|
|||
|
|||
mm:ss format problem
Cool! I had looked at Timevalue but not that way. I'll try it out! Thx!
|
#10
|
|||
|
|||
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. Last edited by ArviLaanemets; 09-23-2019 at 04:54 AM. |
#11
|
|||
|
|||
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?
|
#12
|
|||
|
|||
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. |
#13
|
|||
|
|||
Well, color me stupid! I guess I interpreted General as a title and never thought to enter in a format! Works great: thanks again!
|
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 a range of cells | Dave T | Excel Programming | 1 | 06-01-2016 08:58 PM |
Convert large seconds values to a time format fails with #Value | Sekerob | Excel | 3 | 07-18-2015 01:56 PM |
Procedure for displaying symbols for degrees, minutes, seconds | kinglaird | Excel | 1 | 01-17-2015 02:04 AM |
How-TO format cells (FILL) by comparing cells | zanat0s | Excel | 1 | 07-03-2012 04:27 AM |