Thread: Entering Music Track Times (all)

1. Entering Music Track Times (all)

Time to resurrect <post#=292341>post 292341</post#> from last September.

That thread discussed entering CD track times and totaling the times. I am trying to create something similar for my wife, who knows nothing about Excel. She will not stand for having to enter a time as "0:3:42" for hrs/mins/sec of a track. Having to enter the 0, per that thread, so that Excel treats this as a duration rather than a time of day, is fraught with peril.

That thread also referred to some of Chip Pearson's excellent tutorials on working with dates and times. One of them showed a worksheet change event that would take the number of digits entered, make some reasonable assumptions, and return a formatted number for the date or time of day.

I'm wondering if anyone has applied Chip's approach to the problem of the above post or, better yet, my problem. I want to have a spreadsheet where she enters track times such as "3:42" for 3 mins + 42 secs. It would even be better if she could enter this as "342". What would be shown is whatever is necessary to keep Excel happy - if 0 hours has to be shown "0:03:42", so be it. Of course, it would be better to show it as just "3:42" (without any quotes).

However, like the above poster, I'd need the individual tracks to be summed to a total, so having hours in the total is not out of the question, although unlikely. If a new track is entered (or an existing track's time changed), the sum should be adjusted. I'm not sure if the worksheet change event would cause a recalculation of a cell not in the intersection checked for.

Further, she may also be concerned with dead times at the beginning and end of a track. Although a track may list 3:42 as the play time, there may be 5 seconds at the beginning and 7 seconds at the end where nothing is audible. She may want to enter these times separately (1 col for dead time at the beginning and 1 col for dead time at the end) as just 5 and 7 (probably nothing fancy needed here). There would be another col for "effective play time" which would give the audible time - 3:30 in this case. So, the original entry time, as returned by a worksheet change event, cannot be text since the dead times have to be subtracted from it and times added.

I think Chip Pearson's examples provide about 80% of what I need. I know that I have to use custom formats, per the above post, to get the numbers to appear correctly rather than the TimeValue function which seems to return a time of day. Since my VBA isn't all that good, I was just wondering if anyone knows a source for the other 20% of the code. If not, I'll give it a go.

TIA

Fred

2. Re: Entering Music Track Times (all)

See if this helps.

3. Re: Entering Music Track Times (all)

Hi Jan Karel,

Thanks. I took a quick look at the VBA in the file you sent; I have to go out now. I think that probably has what I need in the subs for the form that place the result back into the cell and format it.

How's the new Name Manager going?

Fred

4. Re: Entering Music Track Times (all)

Attached is an approach using the worksheet_change event to pass a number entered as "mm.ss" (point rather than colon) to time in the format "[m]:ss".

5. Re: Entering Music Track Times (all)

John,

Thanks for the contribution to the "cause" (keeping the wife happy).

A few questions on your code:
- I noticed you used the NumberFormatLocal property to set the format whereas Jan Karel used the NumberFormat. Reading the VBA help didn't help tell me what the diff is (what a surprise). Can you enlighten me on this?
- I noticed your code uses the Value2 property to store the value. Did it make a diff in this case whether Value or Value2 was used?
- I'm also not sure of the value that's being returned. If I enter 6.5 in a cell in cols E-G, the formula bar shows 1/6/1900 12:00:00 PM. I understand this as the "regular" way Excel would show 6.5 formatted as a date. However, the cell shows "9360:00" (w/o the quotes). This seems to be the number of minutes that have elapsed since 1/1/1900 until the value I entered when interpreted as a date serial number. That is, 9360 represents the number of minutes from 1/1/1900 midnight until 1/6/1900 12 noon (which is the date corresponding to a serial number of 6.5). I thought what this was doing was allowing me to enter a number such as 6.5 to mean 6 minutes and 5 seconds (rather than entering it as "0:6:5") but have it formatted as either "0:06:05" or "6:05" or ....

Fred

6. Re: Entering Music Track Times (all)

Name Manager is really close to completion. I'd recommend having a look at the latest version:

NumberFormatLocal accepts a string like "uu:mm:ss" for a Dutch system.

7. Re: Entering Music Track Times (all)

Fred, I use Value2 because in my experience it can be more accurate with very small floating point values, and since minutes are 1/1440th of a day, and a day is the unit of measurement for Excel time recording, I thought it would be safer.

Because the times are converted to true decimal fractions of a day, they will be treated as times starting from midnight Dec 30, 1899, but should display only as "3:42". The code was only designed to have an entry with a single decimal point, it will give an invalid result if you try to enter hours, minutes, and seconds, in a format such as "1.02.45". Also, if you enter a cell in edit mode and don't change anything upon exiting, the Worksheet_Change code will operate on the contents and pretty much wreck the prior value.

It may be that my spreadsheet doesn't work correctly, in testing it seemed to be returning valid results. Submit some examples of where it doesn't work and I'll look some more.

8. Re: Entering Music Track Times (all)

Hi John,

Thks for the response.

Good enough for Value2. What about the diff between using NumberFormat vs NumberFormatLocal which I also mentioned in my last post? Can you enlighten me on the use/preference for one over the other?

As to things that don't seem right, I'm reattaching your workbook (renamed by me) with a few entries I made. If you look at row 7, you can see some very odd looking entries. For example, E7 was entered as 5.5 while F7 was 6.5. E8 was 6.54 as was E9. But when I typed 6.54 again in E8, it was converted as you see. Something odd seems to be in place about converting numbers. This is in Excel 2002.

Fred

9. Re: Entering Music Track Times (all)

Fred, problem is that numbers in the fomat "6.43" are now being converted by Excel to 6.43 days, which is the huge minute count (9259:12) that gets displayed. This isn't happening on my version of the workbook. Did you alter the cell formatting? When I highlight the cell after entering 6.46, the format shows as 12:06:46 AM, i.e., 6 minutes and 43 seconds after midnight.

I'll have to test further for a solution but it you'd like to give it a go, first try altering the code to:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("E:G")) Is Nothing Then
Application.EnableEvents = False
With Target
.NumberFormatLocal = "General"
.Value2 = TimeMinSec(.Value2)
.NumberFormatLocal = "[m]:ss"
End With
Application.EnableEvents = True
End If
End Sub

BTW, Jan Karel explained the reason for NumberFormatLocal; it forces region specific formatting. Since you are in the US, probably unnecessary.

10. Re: Entering Music Track Times (all)

Fred, the problem may be simpler. When I open your version on my machine, it behaves as intended. Perhaps we have some Excel setting difference that causes the problem, though i can't get my Excel installation Options to recreate the problem. Change NumberFormat Local to NumberFormat.

11. Re: Entering Music Track Times (all)

What is the Time Separator in your Control Panel Regional Options?

12. Re: Entering Music Track Times (all)

John,

a couple of things:
- I should have mentioned one other very odd thing in my last post. In the workbook I sent you with that post, I had entered 6.54 in E8 and it appeared as "6:54". I then entered the same in E9 and you saw "6:54" in E9. But when I went back to enter 6.54 in E8, it changed to "9417:36". I did the same in E9 and it changed too. But I clicked undo and it changed back to "6:54". I did realize that the big numbers were due to Excel interpreting the 6 as 6 days. If you look at the formula bar for E8 and E9, they are being interpreted differently: E8 is "1/6/1900 12:57:36 PM" while E9 is "12:06:54 AM". If you change the format to General to see the sample (and then cancel so General doesn't become effective), you'll see E8 as 6.54 but E9 as .00479...
- I did not change any of your formats.
- I checked the format for the cells by right clicking, choosing Format Cells. They all come up as [m]:ss including E8 and E9.
- changing NumberFormatLocal to NumberFormat (in 2 places in your new routine) does not help. But why do you first set the format to "General" and then change it to "[m]:ss"?

I tried your new Worksheet_Change routine. No difference. What is even stranger is that I set breakpoints in your routines, including the function, and no break was recognized (ie, the VBE did not stop anywhere in the WS_C routine). Is this normal behavior in an event routine?

Are you running Excel 2002? I am. I'll have to take this over to our other PC tomorrow where I have Excel 2000. Too late for that now.

To answer Legare's question, my time regional setting is set to the colon : character.

Fred

13. Re: Entering Music Track Times (all)

My time separators are colons.

Fred, I did originally write that code in Excel 2000, but testing in 2002 gives me the same results. The only way I can force an error is to enter the time as 6/1 instead of 6.1. Hmm ... Fred, you wouldn't have Tools | Options | Transition | Transition navigation keys checked?

14. Re: Entering Music Track Times (all)

John,

I don't have the Transition Keys box checked - in XP or in 2000. Not even sure what it does.

I loaded your spreadsheet onto my other PC with Excel 2000. I entered 6.45 into E37. It started as 9417:36 and then changed to 6:54. E35 and E36 were ok.

I also set breakpoints in your code. At first the breaks would be activated. On one of the cells I entered 6.54, I went back and re-entered the data. From that point on, no more break points activated. Even after I closed and reopened the workbook (not Excel).

It also seems that formatting seems to carry over. I entered 6.54 in F27 and G27 and it was changed to 6:54. When I entered 6.54 into H27, it was changed to 9417:36. I don't think col H was formatted generally as [m]:ss. It seems if there was something to the left, Excel would pick up the formatting. I think I've heard that that behavior is "normal". When I look at the cell formatting, it is either General or [m]:ss, depending on if there's an entry to the left in col G.

Seems like some very weird things going on.

Fred

15. Re: Entering Music Track Times (all)

Fred, only columns E, F & G are supposed to do the automatic conversion. There may be other cells in other columns that are time formatted from when I was testing (albeit briefly), but only those columns are supposed to work.

I'm stumped for now.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•