my profile | my password | search | faq & rules | forum home

» Film-Tech Forum ARCHIVE   » Community   » Film-Yak   » Microsoft XL help (Page 1)

This topic comprises 4 pages: 1  2  3  4

Author Topic: Microsoft XL help Joe Redifer You need a beating today Posts: 12859 From: Denver, Colorado Registered: May 99 posted 03-12-2012 10:43 PM                        See what I did there? Y'know, like Simplex XL? Hahaha?? No? OK. Anyway I am new to Microsoft Excel. I am using Office 2011. I have a column full of times and I would like Excel to add these times. Times are listed in minutes and seconds, no hours though I would like Excel to be able to add hours as necessary. Times are listed like this: 7:21 or 22:08 (again, minutes and seconds). So how do I get the total time of the entire column down at the bottom. You Windows guys say Microsoft is crazy awesome and Apple sux so I assume Excel can do this without me having to alter the way I enter times. Walk me through this step by step. Thanks.  |  IP: Logged Chris Slycord Film God Posts: 2986 From: 퍼항시, 경상푹도, South Korea Registered: Mar 2007 posted 03-13-2012 01:56 AM                     By "total time" do you mean a running total of time (like 21:00 plus 4:00 becomes 25:00) or do you mean that you are keeping a total for what time of day it is (21:00 then add 4:00 I get 1:00)?  |  IP: Logged Joe Redifer You need a beating today Posts: 12859 From: Denver, Colorado Registered: May 99 posted 03-13-2012 02:58 AM                        Running total, so 7:21 + 6:38 becomes 13:59 and so on.  |  IP: Logged Dominic Espinosa Phenomenal Film Handler Posts: 1172 From: Boulder Creek, CA. Registered: Jan 2004 posted 03-13-2012 05:10 AM                     Assume the following: G5 is time on, I5 is time off the difference is calculated or the cell is left blank. =IF(24*(IF(G5>I5,I5+1-G5,I5-G5))>0,24*(IF(G5>I5,I5+1-G5,I5-G5)),"") If simply adding time formatted numbers together, say 03:00:00 and 01:00:00 then: =sum(A1:A31)*24 Assuming A1 thru A31 contain these times. Make sure the cell with this formula is formatted "General" and not "Time" as it will want to roll over like a 24hr clock.  |  IP: Logged Joe Redifer You need a beating today Posts: 12859 From: Denver, Colorado Registered: May 99 posted 03-13-2012 02:32 PM                        What?  |  IP: Logged David Zylstra Master Film Handler Posts: 432 From: Novi, MI, USA Registered: Mar 2007 posted 03-13-2012 03:14 PM                     I just tested with Excel 2010 . . . . If you have the fields set as Category "Time" and type "37:30:5" (i.e. HH:MM:SS) you can use the simple sum or addition formula. The thing to remember is the time should be entered into the fields with data for all 3 types - i.e. for 30 minutes 2 seconds you would enter 0:30:02 (or 0:30:2 works too). If you enter a whole number it will multiply times 24 (i.e. 3 becomes 72:00:00), entering more than 60 seconds will result in the hours and minutes being calculated (i.e. 0:0:120 will turn into 00:02:00)  |  IP: Logged Chris Slycord Film God Posts: 2986 From: 퍼항시, 경상푹도, South Korea Registered: Mar 2007 posted 03-13-2012 03:29 PM                     I'll assume you want row #2 to contain the time entries, each of them placed in a separate column in that row just so we can make this easy, and they start from B2 and end at Z2. And I assume you want the total hours to be stored in position B4 1) Have a blank document. 2) Click on the "2" on the left side so you can select the whole row. 3) Click on "format" then "cells" 4) Select "time" on the left 5) Change the format code to be "HH:MM" 6) Click on cell B4 7) Type in "=24*SUM(B2:Z2)" and hit enter 8) Right-click on B4 and select "format cells" then on the far-left side choose "number" then select "general" and click OK I'd put some text in A2 and A4 to tell you that they are for the times entries and total hours entries, respectively but that's not necessary.  |  IP: Logged Joe Redifer You need a beating today Posts: 12859 From: Denver, Colorado Registered: May 99 posted 03-13-2012 04:32 PM                        I don't have a blank document. It already exists. There is no HH:MM, only random numbers in the Time options on the format settings. See: I REALLY have no clue what you guys are saying. This is why I hate Excel with a passion. I am not an office kind of guy. Here is the document I am working on. I am trying to get all of the times in column (not row) E to automatically add up. It needs to automatically re-tally when I enter a new episode in at the bottom as well.  |  IP: Logged Frank Cox Film God Posts: 2234 From: Melville Saskatchewan Canada Registered: Apr 2011 posted 03-13-2012 05:13 PM                        Here you go I wasn't sure where you wanted the total so I put it in cell E2. You can put it anywhere you like. Note that it will currently add everything in column E from cell 3 to cell 1024. If you have more entries than that, increase the number.  |  IP: Logged David Zylstra Master Film Handler Posts: 432 From: Novi, MI, USA Registered: Mar 2007 posted 03-13-2012 05:23 PM                     Your current data is in HH:MM as time of day and not MM:SS Do this: Hold down <Ctrl> and click on column E then F to highlight the both columns Right click -> format cells -> number tab -> choose "Time" from left side -> choose "37:30:55" as the type -> click OK Now, in field F3 type the formula =e3/60 Copy that formula down the entire column Verify that the times in column F look correct (basically your current data is formatted as HH:MM and not MM:SS and /60 converts them) Highlight f3 through f48, right click -> copy Right click in field e3 -> Paste special -> Values (choose the first icon with the 123) - this pastes the corrected times into colum E Delete column F In E49 type =sum(e3:e48) and you should get the total of 11:06:06 If you want I can email you back a copy of the spreadsheet I performed the above steps on. edit: actually try this link: link  |  IP: Logged Joe Redifer You need a beating today Posts: 12859 From: Denver, Colorado Registered: May 99 posted 03-13-2012 06:47 PM                        That works perfectly, thanks! Frank, thanks for the effort but the summed times were incorrect. It should be close to 11 hours by my manual calculations.  |  IP: Logged Frank Cox Film God Posts: 2234 From: Melville Saskatchewan Canada Registered: Apr 2011 posted 03-13-2012 06:52 PM                        I should probably have done a sanity check on it; I didn't notice that the format you had your times in differs from the "official" time format that you can do calculations on. Oh well. Shows you what I know...  |  IP: Logged Joe Redifer You need a beating today Posts: 12859 From: Denver, Colorado Registered: May 99 posted 03-13-2012 08:19 PM                        One more question (and this one should be pretty simple): How do I do a line break in a cell? If I press ENTER/RETURN it just moves to another cell, but I'd like to stay within the cell and have line breaks. I imagine it's just a matter of holding the correct button when I press ENTER/RETURN, right?  |  IP: Logged Dominic Espinosa Phenomenal Film Handler Posts: 1172 From: Boulder Creek, CA. Registered: Jan 2004 posted 03-13-2012 08:36 PM                     ALT+ENTER  |  IP: Logged Joe Redifer You need a beating today Posts: 12859 From: Denver, Colorado Registered: May 99 posted 03-13-2012 09:49 PM                        That does not work. Just goes to the next cell. However Command+ Alt+Return seems to work.  |  IP: Logged

All times are Central (GMT -6:00)

This topic comprises 4 pages: 1  2  3  4

Printer-friendly view of this topic

Hop To: Select a Forum: Category: Operations -------------------- Digital Cinema Forum Film Handlers' Forum Large Format Forum Ground Level Feature Info, Trailer Attachments & REAL Credit Offsets Category: Community -------------------- Film-Yak Film Handlers' Movie Reviews The Afterlife Bob Maar's Joke-A-Thon Category: Classified -------------------- Equipment and Supplies Wanted/For Sale Help Wanted/Seeking Employment Category: System -------------------- Software Beta Testing Forum

The Film-Tech Forums are designed for various members related to the cinema industry to express their opinions, viewpoints and testimonials on various products, services and events based upon speculation, personal knowledge and factual information through use, therefore all views represented here allow no liability upon the publishers of this web site and the owners of said views assume no liability for any ill will resulting from these postings. The posts made here are for educational as well as entertainment purposes and as such anyone viewing this portion of the website must accept these views as statements of the author of that opinion and agrees to release the authors from any and all liability.