Adding/Subtracting Hours and Minutes
I would like to total lists of Hours and Minutes, adding or subtracting,
but not related to a calendar 12 or 24 hour system. The totals need to
be in hours and minutes without losing or gaining the odd minute.
23479:32 plus 10010:59 minus 241:45
The result should be 33248:46
Any help would be appreciated:confused:
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/
A custom format of
will let you work with times up to (but not including) 10,000 h...formula for adding / subtracting two columns .
Can't find formula to add or subtract two or three entire columns in a spread
Let's say we have numbers in two columns, column A and column B from rows 1
In C1, enter:
=A1+B1 and copy down thru C100
Column C will be the sum of columns A & B.
To get the grand sum of columns A & B in a single cell, enter in an un-used
> Can't find formula to add or subtract two or three entire columns in a spread
> Thank you
Try something like:
=SUM(A:A)-SUM(B:B)-S...Formula Subtracting dates to come up with number of days
HELP - does anyone have a formula that I can use to put a
date in a colume to subtract it from a date in another
column for the purpose of determining the number of days
between the two dates? Thank you
see your other post
> HELP - does anyone have a formula that I can use to put a
> date in a colume to subtract it from a date in another
> column for the purpose of determining the number of days
> between the two dates? Thank you
XL stores dates as integer offsets from a base date, so if your dates
are in A1 ...Formula to Subtract to a Limit ?
I need some way to subtract column b from column a... but I do not
want the result to go below zero.
200-300 = 0 (reached zero lower limit)
Is there an excel formula... or perhaps some way to program a little
code to do this? If it's programming, give me a couple clues on how to
enter the code - I'm a programmer but don't know much about Excel
aside from a bit of advanced formula entry.
=MAX(A1-B1,0) and copy down the column
> I need some way to subtract column b from column a... but I do no...Subtract dates in phone call entity
the phone call entity has two dates - actual start and actual end
there is also a field called duration
can someone please help me with a code which subtracts the actual end from
the actual start to give the duration in minutes.
thanks in advance
Look at "Calculating durations" in
http://www.stunnware.com/crm2/topic.aspx?id=JS12. Simply modify the script
to fit your needs.
Michael H�hne, Microsoft Dynamics CRM MVP
Custom Lookup Dialog: http://www.stunnware.co...Unexpected Rounding on Subtraction
If you type in say 179.95 then in the next cell 179 and subtract the latter from the former you get 0.95. Actually, you've got 0.949999999999989. Maybe this is for statistical purposes (although this should just apply to relevant functions), but this is a problem, as I want to create a general formula to convert decimal values into degrees, minutes and seconds
PS I know about the formatting solution
Cheers for any hel
0.95 has no exact binary representation (much as 1/3 has no exact
decimal representation). When you enter 179.95, computers (which do
binary math) see not 179.9...I want to subtract 57 sec. from 1 minute 27 sec. and input 1:27 not 87 sec. help!
This is what I do now
B1=87...... But I want B1 to look like this-1:27
Please let me know the right formula. Thank
Message posted from http://www.ExcelForum.com
You seem to be wanting to have your data in time format but are not
"telling" Excel that. How does Excel "know" that 57 is 57 seconds?
Format as time (e.g.) h:mm:ss
Format as time
Now =B1-A1 returns 0:00:30
Excel records time as a decimal part of 1 day hence to get seconds
from bare numbers we divid...Excel 2003 calculation bug
I think I have found a bug on Excel 2003, but I am not sure if it is fixed.
I have written a very simple "program" in a new clean workbook:
A1 : 30.2
B1 : 32.3
C1 : 1.1
D1 : 1
E1 : =A1-B1+C1+D1
The result is 2.22045E-15..........I suppose it should be zero.
The numbers are so simple, and the formula is straight forward enough that I
don't think it is ignorable. I understand that using ROUND function or check
the option "precision as display" can solve the problem, but is it a little
bit too much to do for such simple work?
Could anyone provide me the bug fix ...Subtract Month
I want to update a column of data type datetime in SQL Server 2000.
i want to substract the 1 from month. i.e.
col1 shoul look like this
Muhammad Bilal wrote:
> I want to update a column of data type datetime in SQL Server 2000.
> i want to substract the 1 from month. i.e.
> col1 shoul look like this
...How do you add or subtract the subject line from the inbox / sent.
If you sign on to outlook and are in your inbox, you get a list of emails
with name: subject: and date. How do you add or subtract the subject line?
I have a friend who does not have the subject line and we can not figure out
how to add it.
Click your right mouse button over the grey column headings in the
Inbox where the Subject heading is missing, select Field Chooser from
the menu that appears and a box will appear containing all the mail
fields, scroll down until you see Subject and then click and hold your
left mouse button on the Subject field and drag it to where you want it
in ...how do I subtract several cells from one total
If A1 is 3500 and cells A2 is 500 and A3 is 100 A4 100 A5 is 100 then how do
I substract A2 thu A5 from A1 without doing each one seperately
in A6 type
"highway" <email@example.com> wrote in message
> If A1 is 3500 and cells A2 is 500 and A3 is 100 A4 100 A5 is 100 then how
> I substract A2 thu A5 from A1 without doing each one seperately
should do it
----------------------------------------------------------------...How do I subtract 20% from one column in Excel and place totals i.
I would like to know if someone knows the formula I use to subtract 20% from
totals in one column on my spreadsheet to show totals in another column on
same spreadsheet. Can anyone help? Spent way to long trying to figure out
assuming totals in A30
(remove nothere from the email address if mailing direct)
"Full Effect Landscaping" <Full Effect
Landscaping@discussions.microsoft.com> wrote in message
> I would like to know if someone knows the formula I use to subtract 2...Adding & subtracting cells (not values)
I have a row of 15 cells with $0 value. I want to start from 15 at the
bottom of this row (for the 15 cell total) and subtract 1 from total of 15
each time a cell value changes its value from $0 to any positive $ amount,
allowing me to know how many $0 cells are sill remaining.
In cell A16 type =COUNTIF(A1:A15,0)
"Nick P" <firstname.lastname@example.org> wrote in message
| I have a row of 15 cells with $0 value. I want to start from 15 at the
| bottom of this row (for the 15 cell total) and subtract 1 from total ...how can I subtract the contents of several cells
I know that by entering SUM=(b1:d1) in the formula bar, this will add the
contents of these cells. How can I do this with subtraction without entering
Alternatively, you could array-enter (CTRL-SHIFT-ENTER or CMD-RETURN):
Note that the second solution won't ignore text.
In article <1C7A240D-3E4B-451A-90C0-E95A67A2DE51@microsoft.com>,
"whiteteeth" <email@example.com> wrote:
> I know that by entering SUM=(b1:d1) in the formula bar, this will add the
> contents of t...how do i do adding and subtracting on excel
Angela, may need more information on what you want to do, but
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
"angela" <firstname.lastname@example.org> wrote in message
This is using a simple formula entered in cell A6
so now cell A6 will display the addition of A3 A4 and A5
similarly you could have a cell with the...Help with time subtraction
This is probably a basic question, but here it is anyway.
The 1st cell I want start time, 2nd cell finish time, 3rd cell will be the
difference in the first to cells in #hours # minutes, or even decimal hours.
| 8:00 AM | 1:30 PM | 5 hrs. 30 min.|
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
=cell2 - cell1, formatted as time for hours/minutes view, or
=(cell2 - cell1)*24 for decimal hours
Ken...........Subtraction question #2
Subtracting cells C2 thru I2
Example: If C2 had 3436, D2 had 3395, E2 had 3376, then I need the answer in
cell J2 to be 60 EVEN IF THE CELLS F2 THRU I2 ARE BLANK.
This formula works fine if I have numbers in ALL cells (C2:I2)
"=MAX(C2:H2)-I2" but when there are blank cell it doesn't count right.
Thanks in advance!
If I have understood your question correctly, this formula entered in J2
will do the trick.
You can't have for instance a number in C2, D2 blank and a number in E2.
The blanks are suppose...How do I subtract todays date from range of dates
I need to make todays date as a constant so when I drag the formula down the
cells used in calculating the cell with todays date does not change.
USe absolute references as in =$A$1
We could help better if we saw you formula
Microsoft Excel MVP
"AHD3" <AHD3@discussions.microsoft.com> wrote in message
> I need to make todays date as a constant so when I drag the formula down
> cells used in calculating the c...Union and Intersect range... but any SUBTRACT?
I see the functions to union and intersect a range, but I don't see a
function to subtract one range from another.
Does anyone know if this function exists, or if there is a VBA function out
there to do this I can paste into my application?
Found in a search of newsgroup archives
search all words: union intersect subtract
Tom Ogilvy posted a SubtractRange subroutine in a
reply 2000-08-06 that should work for you.
Can SKS give me the formula to subtract two dates where the answer is in
years (to 2 decimal places)
eg A1 23/10/92
Answer in the format "xx.yy" years
Found the answer in another newsgroup (it is the datedif function - and does
not seem to documented)
"Rob" <email@example.com> wrote in message
> Can SKS give me the formula to subtract two dates where the answer is in
> years (to 2 decimal places)
> eg A1 23/10/92
> A2 ...Subtracting Two Tables
Is it possible to easily subtract two tables and place the results into
another table? I have two 4x4 tables. The left corners are at A1 and F1.
I would like the result to appear in the corner at A7.
Wayne Watson (Watson Adventures, Prop., Nevada City, CA)
(121.015 Deg. W, 39.262 Deg. N) GMT-8 hr std. time)
Obz Site: 39� 15' 7" N, 121� 2' 32" W, 2700 feet
Solid waste produced to generate electricity per person
over a lifetime:
Nuclear power -- 2 pounds in a coke can
...how do I subtract years and then divide
I am trying to subtract date hired from today's date and then divide the
difference by 365
try this where cell a15 contains the date hired.
"crystal" <firstname.lastname@example.org> wrote in message
> I am trying to subtract date hired from today's date and then divide the
> difference by 365
...Subtract several rows from a total
This is a multi-part message in MIME format.
I have a total in cell C32. I want to subtract numbers in cells C 34-40 =
and have the answer in cell C41. I know that =3DC32-C34 will give me an =
answer but I want several rows to be subtracted. What would my formula =
I have used the insert function feature before but I don't see diff or =
anything that looks like a subtraction function in there. =20
I have a report with column headings that are dates. These are displayed in
mm-yy format. The first column is todays date =date(). The next 11 columns
need to display the previous 11 months. I had set it up as the second column
being =date()-30 and the next column =date()-60, and so on and so forth. The
problem is (which you may have already figured out) is that when we get to a
certain point of the month and on certain months 30 days doesnt necessarilly
equate to the previous month (such as on Jan 31st). In that event, I would
have two Jan-08 columns (which I do not w...subtract 3 numbers and 2 cells
How would I do this, I=100-E4-G4? I have E4 as 97 and G4 as 0 so the answer
should be I = 3. Please help.
In another cell just enter
(remove nothere from email address if mailing direct)
"lmassier" <email@example.com> wrote in message
> How would I do this, I=100-E4-G4? I have E4 as 97 and G4 as 0 so the
> should be I = 3. Please help.