I am copying working with CD metadata and coming across a problem with durations. What is supplied to me is this format 0:00 and I need it to be 00:00. But every time I try to either manually change it or use custom mm:ss it comes out as a totally different number. Know this has something to do with the serial number.... Can someone help me figure this dilemma out? I have thousands of CDs to do for a publisher. Thanks!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Music durations
- Thread starter Heydiddle
- Start date
Jonmo1
MrExcel MVP
- Joined
- Oct 12, 2006
- Messages
- 44,061
So you have some that come in as 00:00 and some that come as 00:00:00 ?
Inconsistent data is the root of all evil in Excel. It really is.
Here's the problem now.
Time has 3 components, hours minutes and seconds.
If excel is presented with a time value, but only given 2 of the 3 time components,
Then it assumes this to be HOURS and MINUTES.
Basically, someone decided that a person is more likely to exclude seconds from a timevalue, instead of hours.
So 2:30 is more likely to be 2 hours and 30 minutes instead of 2 minutes and 30 seconds.
That is why formatting as hh:mm has worked.
But now if some are coming in with all 3 time components provided, then this will no longer work.
You'll need some method to tell the difference between which format has been presented to Excel.
Or even better, work with the source data so that the times are reported the same way for all records. Either 00:00 or 00:00:00, not a mixture of both.
Inconsistent data is the root of all evil in Excel. It really is.
Here's the problem now.
Time has 3 components, hours minutes and seconds.
If excel is presented with a time value, but only given 2 of the 3 time components,
Then it assumes this to be HOURS and MINUTES.
Basically, someone decided that a person is more likely to exclude seconds from a timevalue, instead of hours.
So 2:30 is more likely to be 2 hours and 30 minutes instead of 2 minutes and 30 seconds.
That is why formatting as hh:mm has worked.
But now if some are coming in with all 3 time components provided, then this will no longer work.
You'll need some method to tell the difference between which format has been presented to Excel.
Or even better, work with the source data so that the times are reported the same way for all records. Either 00:00 or 00:00:00, not a mixture of both.
Last edited:
Upvote
0
Excel Facts
Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
What is the maximum duration that you will encounter in any of the records?
Best regards
Richard
Upvote
0
2 things that come to mind about that......
Will you ever encounter times in excess of 1 hour? What happens in the case of (hh:mm:ss) 01:00:02, 1 hour, zero minutes, 2 seconds. Will that not convert to 1 hour, 2 minutes?
If you enter 02:54 and format the cell to hh:mm, obviously it appears as 02:54, but should it be 2min 54seconds or 2hr 54min?
Best regards
Richard
Upvote
0
Similar threads
