I am trying to subtract 12 hours from a date/time cell if the date in B1 is greater than A1. A1 3/3/2010 2030 (formatted: m/d/yyyy hh:mm) B1 3/4/2010 1030 (formatted: m/d/yyyy hh:mm) No matter how I format A1, B1 and C1 I still get a #Value error. I am obviously missing something or leaving something out. Any help is greatly appreciated. Cindy Valid date & times would like: 3/3/2010 20:30 else your date and times are text. -- Regards Dave Hawley www.ozgrid.com "Cindy" <Cindy@discussions.microsoft.com> wrote in message n...

I have a problem with some calculations in a spreadsheet. I have some numbers that have functions tied to them to figure out the value I want to show... Example.... Cell B30 SUM(ROUND(B28,3)*B29 I now have other cells H20 = B30 H19 = H15 H23 = h20 - h19. All cells are showing a currency format. But when you do the subtraction the H23 shows something different (Looks like a rounding issue).... It is simple I want to calculate what is shown in the cell. What do I need to do to do this? Thanks, Rog ...

I have Ecell 2003 and I have just started a Family budget. I have inputed everything and it is working fine,Till I input what I pay for in cash and it also subtracts from my cash balance. I do not want that, I want a clear picture of where I am spending my money, Not to alter something to make it add up to my checkbook. I woould apperciate any help. Thank you I'm certainly confused. When *I* spend cash, it makes *my* cash balance lower. Have you found a way to spend money that doesn't? If so, please elaborate. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a qu...

In column A I have start times (DD/MM/YY HH/MM/SS) and in column B I have the stop times. I am measuring the hours from the difference of these columns for each row, in column C. However, I would like to exclude weekends from the subtraction. I.e Friday 5 pm is the start time, Monday 8 am is the stop time, then the difference would be 15 hours instead of 63 hours. I know how to convert the fractions returned from the difference, into hours/minutes/days, and I also know how to convert dates to "day of week", so unless you guys know of a good idea I was just going to sort the file...

I am having trouble manipulating cells formated to time. I need to be able to add, sum, subtract the entered cells, and then work with the results of the addition etc. To add times and prevent them being converted into days, Cusstom Format as [hh]:mm:ss Regards, Alan. "Mschertz" <Mschertz@discussions.microsoft.com> wrote in message news:3B1A92BC-AA5B-4356-A271-84ACD75E82E4@microsoft.com... >I am having trouble manipulating cells formated to time. I need to be able > to add, sum, subtract the entered cells, and then work with the results of > the addition etc. ...

Money did work, but suddenly when I enter a withdrawal, Money adds it. I've checked the transaction to ensure they were marked as an withdrawal. Where is this occuring and are you using the transaction form or doing it inline. For some reason or another when I try to do it inline, it always messes me up...withdrawls are added and deposits subtracted. I don't think its money per se just how my mind perceives the fill in blanks. So I just use the transaction forms. No need for any negative or subtraction keys. >-----Original Message----- >Money did work, but suddenly whe...

My employer does payroll manually. They look at timecard timestamps and then calculate how many hours I worked. Then they forward that data to the company that prints the paychecks. My employer is often wrong on calculations. I am trying to do a worksheet where I enter my start/stop times and get the correct results to check against employer's calcs. Doing an AM to PM shift is no problem, calculations are correct. However when I try to get hours worked from a Start PM to Stop AM ( let's say 11:09 PM - 7:53 AM) shift all I can get is #######. Does anyone have a tip on how to do ...

could somebody please point me in the right direction here. Im tryin subtract 30 minutes form a cell containing a number of hours an minutes formatted using [h}:mm custom cell forma -- MajorGeneralDe ----------------------------------------------------------------------- MajorGeneralDes's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=370 View this thread: http://www.excelforum.com/showthread.php?threadid=31945 Any of these will work: =A1-"00:30" =A1-TIME(,30,) =A1-1/24/2 HTH Jason Atlanta, GA >-----Original Message----- > >could somebod...

Hi I have problem with Excel (2010) subtracting dates. The date1 is entered into COLUMN L and a second date entered into COLUMN U when information sent. The end formula in COLUMN W (below) works fine until there is no date posted in COLUMN U (i.e. blank) then always sees as - figure and shows "LATE". Please help - would like status to show NULL in COLUMN W if nothing in COLUMN U entered. Thanks Potsy Formula in Column V: =IF(U334>0,U334-L334,"") Fomula in Column W: =IF((V334=0),"ON TIME",IF((V334<0),"EARLY",IF((V334>0),"LATE",I...

I'm trying to subtract columns in a macro, but since the resulting file will be exported in *.csv format, I only want to subtract the rows that have data in them. Can someone tell me how to do that? Thanks so much! ...

how to subtract one row from the other? Use a simple formula like =A1-A2 Copy this formula to the right as far as you need to go. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "prasad" <prasad@discussions.microsoft.com> wrote in message news:EADC60A5-9995-4E52-9A58-7A7FAFC8C69E@microsoft.com... > how to subtract one row from the other? ...

i have a problem at work where i have to subtract one set of hours and minutes from another, i.e. 13234:34 - 1234:58. The problem that I have is that excel does not recognise above 9999:99. Does anybody have an idea as to how I can do this, be it a conversion, macro or a simple aside formula? -- Format the cell as [h]:mm good as long as the result is zero or positive. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "M...

hi I want know that why there is NOT any relationship between access and excel and how I can sum and subtract 2 or 3 filds in access in any record thanks hadi_khodaparast@yahoo.com Hello Hadi - I'm not sure I understand exactly what you are asking, but Excel is *not* a 'front end' for an Access database. You can use Excel to query the db & copy Access data into a workbook in several ways. That copy of the data can be used for calculations, but the data *doesn't* get changed in the Access file. Although the modified results can be imported to Access it may very well be t...

Hello! I'm trying to do the following: In C8, users enter in a date. In K30, I want to display the number of days until the end of the month, using the C8 field as the start date/month. Example: User enters 10/8/2004 in C8 K30 should equal 23. It also needs to accomodate the calendar year 2005, and so on. Is this possible? 2ND QUE: The above answer may also take care of this, but what is the date format if I want to use a subtraction or matching formula. Example: A1 = 2/1/2004 I have a column of months (J), and a corresponding set of values (K). I...

Hi Kelly if you want to subtract the value in B1 from the value in A1 and have the answer display in C1 then in C1 type =A1-B1 Hope this helps Cheers JulieD "Kelly" <Kelly@discussions.microsoft.com> wrote in message news:5DF4C83F-6BF1-40A8-BC8A-692058A5C70B@microsoft.com... > ...

Here's what I'm wanting to do and it's driving me nuts trying to get this setup. I have a worksheet for calculating my Weight watchers points and progress and such. I have almost everything working except this one part. I want to have it compare my total daily points (25) to my total allowed points (28). If my total daily points are higher than my total allow points, I would like it to calculate how many I'm over and then subtract those from my weekly flex points (35). I believe this is possible but I'm not sure if I'm using the correct command. Let me know if I di...

This is a timesheet: A1 B1 C1 D1 E1 F1 Time 9:00 am 12:00 pm 9:00 am 12:00 pm OFF Total 3 3 #Value! I have time in and time out with total hours caculating. The formula I am using for total hours is a simple C1-B1 with HH:MM format. I need to enter OFF, SICK, OUT for days that the employee is not in but I get an error value. How can I make it so that the formula adds the hours entered and ignores any other values such as OFF, SICK, OUT. >The formula I am us...

I have a column with numbers that is totaled at the bottom. The Auto Sum formula correctly adds all the cells. I tried to make two of the cells negative so that they would be subtracted. But, the cell amount is being doubled before it is subtracted from the total. So, if the cell amount is 50, for some reason 100 is being subtracted. I have all cells formatted as numbers, not general or text, and have checked for hidden formulas (there are none). Here is my example to show what is happening: =SUM(B2:B6) where B2 = 5 B3 = 5 B4 = 10 B5 = 15 B6 = 15 My total is correct at 50...

Birthday = 5/1//1946 I am using a formula to get the age of a person =(TODAY()-Birthday). This returns a number 22272. I know that I can format that number and return the correct year - 60. But I want to calculate off of that number. I am doing an IF this year = certain age, I will get a certain text script. But my formula does not see the =(TODAY()-Birthday) as a 60. How do I show an age in years that can be calculated from? Thanks Dave =DATEDIF(Birthday,TODAY(),"y") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) ...

I have two worksheets in the same document with a long list of Email addresses. I want to use a Query to create a new list that has only the Email addresses that are not present in both lists (i.e. subtraction). In Excel 2003 I tried going to Data, Import External Data, New Database Query and it made me select another worksheet. When I selected the one I was working on it said there are no tables in the worksheet. (Whats the difference between a column of data and a table?) Anyway, am I going about this the proper way to perform a Query? Thanks for any assistance you can offer, Bria...

I have the following Pivot table created: --------------------------------------------------------------------------- Sum of HEAD_COUNT DEPT PER_GROUP TYPE HQ HRD ED PD CFD --------- ---- -- --- -- -- --- ACCOUNTING 1APPROVED 0 0 0 0 0 0 2ACTUAL 0 0 0 0 0 0 ACCOUNTING Total 0 0 0 0 0 0 ADMINISTRATIVE 1APPROVED 0 1 0 0 0 0 2ACTUAL 0 0 0 0 0 0 ADMINISTRATIVE Total 0 1 0 0 0 0 CORPORATE SUP 1APPROVED 1 0 37 1 8 7 2ACTUAL 1 0 35 1 8 7 CORPORATE SUP Total 2 0 72 2 16 14 ----------------------...

I'm having some success with the addition part but not with the subtracting part! I have an Excel spreadsheet that is used to keep track of time worked by staff on a weekly basis and I need to keep track of the overtime worked. For example, one staff member has worked 40 hours, 54 minutes and 25 seconds in a week (displayed in a cell as 40:54:25 with the cell formatted as Category: --> Time and Type:--> 37:30:55). I then went to subtract 40 hours from the total hours worked to get the overtime displayed and thats where I'm having the problem. I tried the format of "=F9 -...

I'm attempting to create an Excel timeline of battles and major eveins in United States history. I want one of the column's to show how many years it has been since that date. For example, say A6 has July 4, 1776. Cell A1 has the formula =Today() Every time I use the formula =Year(A1)-Year(A6) I get an error message in the cell. Sadly, dates only go back to 1900. -- Gary''s Student "Bob Martin" wrote: > I'm attempting to create an Excel timeline of battles and major eveins in > United States history. I want one of the column's to show how man...

Hi, I have a section of a worksheet that just needs to show working days between two dates. For example, an order was entered on 9/2/07 and complete on 9/15/07 The formula =NETWORKDAYS(A1, A9) works perfectly except that until the "completed date" ise filled in, a negative number appears. Is there a way to have that remain blank until both dates have been entered? Thanks! Hi Edward, change your formula as =IF(A9="","",NETWORKDAYS(A1, A9)) -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER...

Hi there, I am trying to keep track of my hours at work, as I need to make up a day I spend at hospital during the course a month. I have put together a sheet (sort of a cut-down version of my department's timesheet), with day, month, in and out. What I want to do is subtract 30 minutes from each standard work day, so I end up with 8 (or however many hours worked) minus my half hour lunch break. Any help would be greatly appreciated. Regards, Glenn Auckland, new Zealand Glenn Stick 0:30 in a cell and name it lunch_break. Then use = A1-lunch_break, where A1 contains your times be...