[users] Re: Calc: Calculating Time Totals for a Time Card
Thanks Theo! That did the trick! Amichai. On Tue, Mar 1, 2011 at 21:32, Theo Gransier theograns...@home.nl wrote: =sum(B1-A1) -- To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: sy...@openoffice.org with Subject: help
[users] Page Setup for a Template
Hello, I have created a template in Writer and saved it to a shared folder so I can use it both on my desktop PC and my Netbook. I have added a frame and some text inside of it as the footer. I can use the template without any problem on my PC, but when I open the same file with OOO Writer on my Netbook, the frame and text at the footer are misplaced - lower than it should be. If I create a document on my Netbook using this template, save it, and then open it on my desktop - the problem persists. The only way for my to override it would be to create a document on the Netbook without the template, then copy the text to the template on the desktop I really need this to work on my Netbook, since I have to print document while I am on the road or at someone's office. I guess it's related to the page setup, but it seems to be the same. Is there a way I can export the settings from my desktop's OOO and import them to my Netbook OOO? Thanks! Amichai. -- To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: sy...@openoffice.org with Subject: help
[users] Calc: Calculating Time Totals for a Time Card
Hello, I have a Calc spreadsheet I use to keep track of time, kind of a time card. I use the 24 Hrs. format. I have a Sheet for each client for the current month (so it would include 31 rows maximum, not including the first one used for the headers): Column A has the Start Time (i.e.: 09:15) Column B has the End Time (i.e.: 13:25) Now, I want Column C to have the total time for each row, and another cell to have the total time spent for this client that month (say, H3) I guess I'd have to sum the total for C2:C32, but I can't figure out how to calculate it as time and what to put as a formula in H3. Thanks! Amichai. -- To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: sy...@openoffice.org with Subject: help
[users] Coupon Generator?
Hello, I would like to prepare the coupon with, say, OpenOffice Draw and generate a number of identical coupons (based on a template) and insert to a specific area a unique code or number. I found the unique number part - I'll use 'uuid' to generate a bunch of codes (say a 100), redirect them to an ASCII file, one code per line, then I'd like to import each code to the respective area in the coupon template. How do I do this with OpenOffce? Thanks! Amichai. -- To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: sy...@openoffice.org with Subject: help
Re: [users] Customer Billing Formula
Thanks guys, But - none of it worked... I'll describe my needs again(disregard the last one): I have a customer with an hourly rate of 150. Here is what I entered in the sheet: A1: Date (Header) - Column A formated as Date (DD-MM-) B1: Time (Header) - Column B formated as Time (HH:MM) G1: Subtotal - how much to charge this month (Header) A2: The first line with data - the date of the first call this month B2: The duration of the call - 00:45 (forty five minutes) What I want to achieve: H1 should show me how much I have earned from this customer till now, so I need it to multiply 150 by the time I spent for each line (B2:B32)*150 - or something like that... Until now I done this with a pocket calculator, for example: 0.75 (00:45)+0.25(00:15)+1.25(01:15)*150 would give me the sum of 188.50 I just want H1 to reflect that sum if I calculate 150 * (B2:B32) I hope it is now clearer... Thanks! Amichai. On Thu, Jan 13, 2011 at 21:33, openoffice.mbou...@spamgourmet.com wrote: Dan Lewis - elderdanle...@gmail.com wrote: On Thu, 2011-01-13 at 12:48 +0200, Amichai Rotman wrote: Hey All, I have a bunch of customers I give phone support to. I'd like to create a spreadsheet to calculate how much I should charge them each month. Each customer has a different hourly rate. So, here is what I want to do: Sheet per customer For each customer I enter the following: Column A: Date Column B: Time (in minutes - HH:MM, 24hrs format) Column H: A formula to multiply the contents of B2:B32 times hourly rate Obviously I need help with that formula... Thanks! Amichai. Have you set up a spreadsheet the way you stated it above? Is Column A formatted for dates? Is Column B formatted for time using the HH:MM format? Format for entering a formula in a cell: 1. Always begin the formula with an equal sign =. 2. Enter A2 after the equal sign. 3. Enter an asterisk after the A2. 4. Enter the hourly rate R followed by the Enter key. Do this in cell H2. Then use the up key to move the outlined rectangle back to H2. The formula in the window should be =A2*R. (Yes, it is this simple.) Not quite that simple, unfortunately. If the cells are formatted as HH:MM (as Amichai says they are), the numeric value of the cell is the fraction of a day that time represents, not the number of minutes. So what appears as 00:30 (30 minutes) is actually 0.0208... (=30/(24*60)) as far as any formula is concerned. =A2*60*24*(Rate_per_minute) should do it - A2*60*24 converts the time into a number of minutes, then multiplied by your minutely rate. I'm not sure how reliable that would be if A2 is greater than 24 hours, but that probably isn't an issue to you (unless you get a lot of very long calls!) You may want to put the minutely rate in another cell, say J1, and use the following in H2: =A2*60*24*$J$1 Then you can easily change the rate for each customer. Using $J$2 (instead of just J1) ensures that J1 is used in every line when you copy the formula to other cells (as Dan describes below). Entering a formula into adjacent cells: 1. Place the outlined rectangle in the first cell. 2. Move the cursor over the small square at the bottom right corner (vortex) or the outlined rectangle. The cursor becomes a small plus sign +. 3. Drag and drop the plus sign + to the last cell. This process can be used to copy a formula in a vertical or horizontal range of cells. Begin at cell H2 and drag down to H32. Click any of these cells to see the formula entered into that cell. Another suggestion: Do this for one sheet. Also enter the Headings in cells A1, B1, and H1. Now highlight a block of cells containing columns A through H and rows 1 through 32. A1 should be at the top left of this block of cells, and H32 should be at the bottom right of it. Copy this. In each of the sheets, paste this block of cells into cell A1. You have now formated the sheets and entered the formulas in column H. Now to modify the formulas of each sheet: click cell H2. The formula appears in the window above the column headers. Change the rate to what you want in that sheet and click the green check mark to the left of the window. (It only appears after you change something in this window.) Now drag and drop the formula down to cell H32. Do this for all the cells. You should be good to go. Dan - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
Re: [users] Customer Billing Formula
Thanks Mark! That one did the trick! Now I have a file with a sheet per customer with their hourly rate and I can find out at a glance how much I've mde till now and how much I'm supposed to make by the end of the month. Amichai. On Fri, Jan 14, 2011 at 18:19, Richard Saunders richa...@well.com wrote: Why are you sending these e-mails to me? R On 1/14/2011 9:09 AM, openoffice.mbou...@spamgourmet.com wrote: I missed the bit saying it's an hourly rate, and assumed minutely rate since you mentioned the time being in minutes; my mistake there. Also used column A instead of B for the time. In that case, putting in cell H2: =B2*24*150 will give the cost of the call logged in that line - B2*24 converts the time into a number of hours, then multiplied by your hourly rate (150). Again, I would suggest putting the hourly rate in a separate cell and referring to that, so that it is easy to change. Otherwise, you have to remember to copy the formula down each time the hourly rate is changed. So, with the hourly rate (150 in your example) in cell J1, you'd have in H2: =B2*24*$J$1 To get the total in H1, simply add up all the rows: =SUM(H2:H65536) If you don't want the cost of each line itemised, it should be possible to add up the times and then multiply that total by the hourly rate: =SUM(B2:B65536)*24*150 or: =SUM(B2:B65536)*24*$J$1 Personally, I'd rather have the cost of each line as well as the total though, as it would be easier to track down errors in the data (e.g. if the total is ridiculously large, which line is the problem in). Hope that helps. Mark. Amichai Rotman - amic...@iglu.org.il wrote: Thanks guys, But - none of it worked... I'll describe my needs again(disregard the last one): I have a customer with an hourly rate of 150. Here is what I entered in the sheet: A1: Date (Header) - Column A formated as Date (DD-MM-) B1: Time (Header) - Column B formated as Time (HH:MM) G1: Subtotal - how much to charge this month (Header) A2: The first line with data - the date of the first call this month B2: The duration of the call - 00:45 (forty five minutes) What I want to achieve: H1 should show me how much I have earned from this customer till now, so I need it to multiply 150 by the time I spent for each line (B2:B32)*150 - or something like that... Until now I done this with a pocket calculator, for example: 0.75 (00:45)+0.25(00:15)+1.25(01:15)*150 would give me the sum of 188.50 I just want H1 to reflect that sum if I calculate 150 * (B2:B32) I hope it is now clearer... Thanks! Amichai. On Thu, Jan 13, 2011 at 21:33,openoffice.mbou...@spamgourmet.com wrote: Dan Lewis - elderdanle...@gmail.com wrote: On Thu, 2011-01-13 at 12:48 +0200, Amichai Rotman wrote: Hey All, I have a bunch of customers I give phone support to. I'd like to create a spreadsheet to calculate how much I should charge them each month. Each customer has a different hourly rate. So, here is what I want to do: Sheet per customer For each customer I enter the following: Column A: Date Column B: Time (in minutes - HH:MM, 24hrs format) Column H: A formula to multiply the contents of B2:B32 times hourly rate Obviously I need help with that formula... Thanks! Amichai. Have you set up a spreadsheet the way you stated it above? Is Column A formatted for dates? Is Column B formatted for time using the HH:MM format? Format for entering a formula in a cell: 1. Always begin the formula with an equal sign =. 2. Enter A2 after the equal sign. 3. Enter an asterisk after the A2. 4. Enter the hourly rate R followed by the Enter key. Do this in cell H2. Then use the up key to move the outlined rectangle back to H2. The formula in the window should be =A2*R. (Yes, it is this simple.) Not quite that simple, unfortunately. If the cells are formatted as HH:MM (as Amichai says they are), the numeric value of the cell is the fraction of a day that time represents, not the number of minutes. So what appears as 00:30 (30 minutes) is actually 0.0208... (=30/(24*60)) as far as any formula is concerned. =A2*60*24*(Rate_per_minute) should do it - A2*60*24 converts the time into a number of minutes, then multiplied by your minutely rate. I'm not sure how reliable that would be if A2 is greater than 24 hours, but that probably isn't an issue to you (unless you get a lot of very long calls!) You may want to put the minutely rate in another cell, say J1, and use the following in H2: =A2*60*24*$J$1 Then you can easily change the rate for each customer. Using $J$2 (instead of just J1) ensures that J1 is used in every line when you copy the formula to other cells (as Dan describes below). Entering a formula into adjacent cells: 1. Place the outlined rectangle in the first cell. 2. Move the cursor over the small square at the bottom right corner (vortex) or the outlined rectangle
[users] Customer Billing Formula
Hey All, I have a bunch of customers I give phone support to. I'd like to create a spreadsheet to calculate how much I should charge them each month. Each customer has a different hourly rate. So, here is what I want to do: Sheet per customer For each customer I enter the following: Column A: Date Column B: Time (in minutes - HH:MM, 24hrs format) Column H: A formula to multiply the contents of B2:B32 times hourly rate Obviously I need help with that formula... Thanks! Amichai.
Re: [users] [SOLVED] OOo Write - Letterhead for my Business Template
I was able to fix it by changing the Anchor setting to To page. Suddenly the Rounded Rectangle graphic appears around the text area on every page automatically... Thanks! Amichai. On Sun, Dec 19, 2010 at 20:12, Barbara Duprey b...@onr.com wrote: You can adjust the size and position of the frame regardless of where it's anchored, it doesn't need to be contained physically within the header. You'll want the wrap set to in background too, of course, so it doesn't affect text placement. The only way I know to have something on every page is to have it associated with the header or footer; text area content can't be made part of the page style, I don't think. On 12/19/2010 10:07 AM, Amichai Rotman wrote: That is a nice idea, but I need the frame to be positioned around the main text body area. Isn't there a way to edit the default style for a page to include a frame by default on every page, similar to the Header / Footer ? Thanks! Amichai. On Sat, Dec 18, 2010 at 21:47, Barbara Dupreyb...@onr.com wrote: On 12/18/2010 9:52 AM, Amichai Rotman wrote: Hello all, I am trying to create a template for my private business to serve me as a letterhead. I would like to have it on my Ubuntu box and write the documents using it as a template, as opposed to sending it to a print shop and have to find room to keep thousands of papers I was finally able to figure out how to use the shapes to create a rounded frame around the body of the document (chose the rectangle with the rounded edges, pulled it over the entire area, set the color as invisible, wrapped it to the page and set it as background). It all worked well, except when I start a new page, it comes without the frame... Is there a way I can set a page frame with rounded edges and make it behave as the page itself (save it as a style or some such)? Thanks! You should be able to anchor the frame in a header or footer to make it repeat on each page. - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
Re: [users] OOo Write - Letterhead for my Business Template
That is a nice idea, but I need the frame to be positioned around the main text body area. Isn't there a way to edit the default style for a page to include a frame by default on every page, similar to the Header / Footer ? Thanks! Amichai. On Sat, Dec 18, 2010 at 21:47, Barbara Duprey b...@onr.com wrote: On 12/18/2010 9:52 AM, Amichai Rotman wrote: Hello all, I am trying to create a template for my private business to serve me as a letterhead. I would like to have it on my Ubuntu box and write the documents using it as a template, as opposed to sending it to a print shop and have to find room to keep thousands of papers I was finally able to figure out how to use the shapes to create a rounded frame around the body of the document (chose the rectangle with the rounded edges, pulled it over the entire area, set the color as invisible, wrapped it to the page and set it as background). It all worked well, except when I start a new page, it comes without the frame... Is there a way I can set a page frame with rounded edges and make it behave as the page itself (save it as a style or some such)? Thanks! You should be able to anchor the frame in a header or footer to make it repeat on each page. - To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
[users] OOo Write - Letterhead for my Business Template
Hello all, I am trying to create a template for my private business to serve me as a letterhead. I would like to have it on my Ubuntu box and write the documents using it as a template, as opposed to sending it to a print shop and have to find room to keep thousands of papers I was finally able to figure out how to use the shapes to create a rounded frame around the body of the document (chose the rectangle with the rounded edges, pulled it over the entire area, set the color as invisible, wrapped it to the page and set it as background). It all worked well, except when I start a new page, it comes without the frame... Is there a way I can set a page frame with rounded edges and make it behave as the page itself (save it as a style or some such)? Thanks!
Re: [users] Inserting Dates Automatically into a Column
On Wed, Nov 3, 2010 at 21:21, Rob Clement r...@robbev.com wrote: On 03/11/2010 12:55, Amichai Rotman wrote: Hi All, Continuing my last question: I'd like to create a Sheet for each month. In column A of each sheet I'd like to automatically generate the specific month's dates: Is there a wizard I can use to specify the dates range (say: Nov 2010 till December 2010) and it will create a Sheet for each month with the dates automatically entered in column A - from A2 to A31 (or 30, if that moth is shorter). Of course I want to use it on a different file for next year too I hope I was clear enough... Thanks! Amichai I think the easiest way is this. 1) Create a start spreadsheet. 2) On the top line cell A1 type in the 1st date in the month e.g. 1/11/10 3) On the second line below the 1st date set a cell a2 with = a1 + 1. The cell will show 2/11/10 4) Copy cell a2 downwards for as many days as you require and the dates will appear. 5) In a new spreadsheet copy and paste the 31 days you require from the first spreadsheet. Change the date at the top of the column to be whatever start date you require and all the others will appear. I hope that it clear Thanks Rob Thanks! This does provide some automation. What I was hoping for was some kind of a wizard like the ones for generating a yearly calendar - but under OOO Calc - will generate a sheet for each month with the correct dates (if a month has 28 days - it will generate it automatically, without me having to check first...) I would be pretty happy with some function I enter at the top of the column saying Generate the dates for November 2010. -- .::. Amichai Rotman Registered Linux User#: 201192 [http://counter.li.org/] Registered Ubuntu User #12851 [http://ubuntucounter.geekosophical.net] .::.
[users] Combining Mathematical Functions in a Spreadsheet
Hello, I am trying to create a spreadsheet to keep track of my work ours. This is what I have: Column A - Number of hours I've worked that day - X Column B - The amount I get paid per hour - Y Column C - The amount I get for bus fair per day - Z Column G - Should be the result (in currency format) of X multiplied by Y plus Z equals T. I was able to enter the following in column G: =PRODUCT(A2:A30,B2:B30) to get the result of money I get paid for the number of hours I worked. My problem is: What do I have to write to add the amount I get for a round trip by bus for that day. G has to be the final gross amount of money I get paid for that month. Thanks! -- .::. Amichai Rotman Registered Linux User#: 201192 [http://counter.li.org/] Registered Ubuntu User #12851 [http://ubuntucounter.geekosophical.net] .::.
Re: [users] Combining Mathematical Functions in a Spreadsheet
Thanks Dan! That last suggestion did exactly the trick! I was using a comma instead of a semicolon - and got an error. Thanks! On Wed, Nov 3, 2010 at 13:18, Daniel Lewis elderdanle...@gmail.com wrote: (A1:A31;B1:B31) + SUM(C1:C31) -- .::. Amichai Rotman Registered Linux User#: 201192 [http://counter.li.org/] Registered Ubuntu User #12851 [http://ubuntucounter.geekosophical.net] .::.
[users] Inserting Dates Automatically into a Column
Hi All, Continuing my last question: I'd like to create a Sheet for each month. In column A of each sheet I'd like to automatically generate the specific month's dates: Is there a wizard I can use to specify the dates range (say: Nov 2010 till December 2010) and it will create a Sheet for each month with the dates automatically entered in column A - from A2 to A31 (or 30, if that moth is shorter). Of course I want to use it on a different file for next year too I hope I was clear enough... Thanks! -- .::. Amichai Rotman Registered Linux User#: 201192 [http://counter.li.org/] Registered Ubuntu User #12851 [http://ubuntucounter.geekosophical.net] .::.
