Skip navigation

Lookup & H:M:S:MS format in numbers

285 Views 4 Replies Latest reply: Jan 2, 2013 5:15 AM by Jerrold Green1 RSS
Hugh Dunlop Calculating status...
Currently Being Moderated
Dec 30, 2012 8:33 PM

So I am trying to track time on ice (TOI) for our hockey team, and then translate that data to a spreadsheet. Ultimetaly what I am hoping is that there is some kind of lookup function that will allow me to pull find multiple shift times all at once. This is what the data our computer gives looks like:

 

 

start time

end time

category

Nth instance

# descriptors

descriptors...

shift time

00:10:54:47

00:12:40:53

14 WESTON

1

0

 

0:1:46:06

00:10:55:25

00:12:40:29

20 RUTLEDGE

1

0

 

0:1:45:04

00:10:59:53

00:12:36:69

19 PAQUETTE

1

0

 

0:1:37:16

00:10:59:84

00:12:36:92

15 MATHIESON

1

0

 

0:1:37:08

00:11:01:13

00:12:37:13

23 SARTORIO

1

0

 

0:1:36:00

00:11:36:52

00:12:00:62

NZ

1

0

 

0:0:24:10

00:11:55:88

00:12:03:88

Faceoff

1

0

 

0:0:08:00

00:11:55:88

00:12:03:88

Faceoff Win

1

0

 

0:0:08:00

00:11:56:88

00:12:29:55

15 MATHIESON LIVE

1

0

 

0:0:32:27

00:11:56:88

00:12:29:55

19 PAQUETTE LIVE

1

0

 

0:0:32:27

00:11:56:88

00:12:29:55

23 SARTORIO LIVE

1

0

 

0:0:32:27

00:11:56:88

00:12:29:55

14 WESTON LIVE

1

0

 

0:0:32:27

00:11:56:88

00:12:29:55

20 RUTLEDGE LIVE

1

0

 

0:0:32:27

00:11:57:88

00:12:28:55

Live

1

0

 

0:0:30:27

 

So Start & End Time are obviously where the shift/event begins & ends on the video. Catergory is the player/event name. Nth instance is his shift #. Now here is where I am having problems. Normally I would just use the regular lookup function to get what I need, however our kids have more anywhere between 20-30 shifts throughout the game. Is there a way to lookup based on 2 search criteria? Where I am looking for 14 WESTON LIVE instance 1, and then instance 2 and so on & so on.

 

 

 

Another issue I am havign trouble with is getting Numbers to recognize that all these times are durations. I have figured out how to reformat to text so they look the same and not in a 24hr mode or something, but it's when I am trying to get shift times I have trouble:

 

 

00:59:23:20

00:59:42:73

DZ LIVE

76

0

 

0:0:19:53

00:59:25:20

00:59:40:73

DZ

64

0

 

0:0:15:53

00:59:40:73

00:59:41:50

NZ

118

0

 

0:0:00:37

00:59:41:50

01:00:01:86

OZ

62

0

 

-1:11:39:24

00:59:57:70

01:00:41:50

20 RUTLEDGE

15

0

 

-1:11:16:20

00:59:58:26

01:01:05:31

02 SWAB

11

0

 

-1:10:52:55

01:00:01:86

01:00:20:27

DZ

65

0

 

0:0:18:01

01:00:02:58

01:00:58:58

23 SARTORIO

11

0

 

0:0:56:00

01:00:02:92

01:00:58:29

15 MATHIESON

8

0

 

0:0:54:57

 

So as you can see it's becuase of the change from 0 hr to 1hr, and it's because I am in text that it wont recognize it's a duration/time #. So I can get thing of each problem corrected, but then it creates another one. I am hoping there are many other people out there smarter than myself that can help me??

 

Thanks,

Hugh

numbers, OS X Mountain Lion (10.8.2)
  • Jerrold Green1 Level 7 Level 7 (28,190 points)
    Currently Being Moderated
    Dec 31, 2012 11:39 AM (in response to Hugh Dunlop)

    Hugh,

     

    I'm having trouble duplicating your problem description, but I think we can work through this if we go one step at a time.

     

    If I copy the table in your post and Paste it into Numbers, the duration columns are converted to Durations in Numbers but they are corrupted because of the camera format not being recognized. If I copy the table in your post and Paste it into Pages I get a 100% replication of what I see in your post, and it's all formatted as Text. If I then Copy the table in Pages and Paste into Numbers, it stays as Text. (This is a discovery for me.)

     

    With the data in Numbers as Text, we can convert to anything you want, and we can do the summaries that you want. We just have to get off on the proper foot.

     

    Jerry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Jan 2, 2013 2:21 AM (in response to Hugh Dunlop)

    Hi Hugh,

     

    Units for Duration, as can be seen in the Cell Format inspector image below are Weeks, Days, Hours, Minutes, Seconds and Milliseconds.

    Picture 2.png

    In the format shown, each unit except ms is separated from the larger unit before it by a colon. The separator between seconds and milliseconds is a period (in locations where the period is the decimal separator.

     

    I think this is where your table and formulas are going wrong.

     

    As written in your example above, Numbers interprets "01:00:06:40" as "one day, zero hours, six minutes, forty seconds" and "00:59:41:29" as "zero days, fifty nine hours, forty one minutes, twenty nine seconds." Subtracting the second from the first gives the correct result:  -35h 34m 49s.

     

    The fix is pretty simple: replace the last colon in each duration with a period. You may also need to append a zero to the end of each time to produce three digits following the decimal.

     

    Getting to that state may prove a bit complicated. Work with a copy of your raw data, placed into a separate document, then paste the results back into your working document, using Paste Values. Here's a conversion formula that will produce a text string easily reconverted to Duration while preserving the set units.

     

    The example uses the first two values in your example table above. The formula shown is entered in D2, then filled right to E2. Fill both to the end of data in columns B and C.

    Picture 3.png

    After pasting these two columns back into the original table (using Paste Values), you can re-format the cells to Duration using the format shown or the 00:00:00.000 format choice in the inspector.

     

    For convenience, here's a copy/pasteable copy of the formula above:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,":","s",3),":","m",2),":","h")&"0ms"

     

    The table below shows my results for the upper part of your sample table above. I haven't searched out the small difference in results to this point.

    Picture 4.png

     

    Regards,

    Barry

  • Jerrold Green1 Level 7 Level 7 (28,190 points)
    Currently Being Moderated
    Jan 2, 2013 5:15 AM (in response to Hugh Dunlop)

    Hugh,

     

    As I stated in my first response, Numbers does convert your imported data to Duration, it just doesn't do it properly because it isn't a recognized format. Your imported data does not stay as text, not on my system and I suspect not on yours. This is easy to confirm if you look at the Cells Inspector with one of your column A cells selected. You don't have to guess at it.

     

    Barry has given you a possible workaround. My only concern is that he and I would be guessing as to the intended weight of the last pair of digits in the Start Time and End Time readings from the recorder. Hundredths of seconds is a good guess since some of them exceed 60, a common maximum value for the least significant division, often the frame count in a given second.

     

    So, going with that assumption about the smallest units, here's an alternate workflow for you...

     

    Take the data in the form that you posted it in your forum question and Paste it into Pages or other RTF WP app.

     

    Copy the Pages Table and Paste into Numbers

     

    Add the columns that are shaded in my example below and apply the expressions shown:

    Screen Shot 2013-01-02 at 8.11.13 am.png

    Regards,

     

    Jerry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.