I'm trying to do the following:
In the attached file, I'd like to subtract whichever is the earliest time between B4 or C4 from A4, then B5 or C5 from A5, etc.
I assume I need a conditional "if" statement but I'm not sure if that will work.
Attached File : Time Subtraction Example.xls
Ecel 2000
If Ii subtract time
eg 22:00-01:00 I get ############### wrong
If I subtract 22:00 23:00 I get 1 HR correct
1st problem concerns entering data as text and it being converted to time. I have found the formula 00:00 which does an excellant job of converting. However, when I want to subtract, (=a2-a1) for example, it treats the time as a number. Sometimes it works, but if the hours are different, say 23:30- 22:10, it will subtract 2330-2210! Is there a way to make this work?
2nd problem, related to the first is when I try to avg a column of times, I get a similar effect.
I have data that when added together will have decimals of .1 or .2. Any decimal great than .2 becomes an integre of 1 and is added to the other integres. for example: 1.1, 2.0, 3.2, 5.1=12.1.
I now want to subtract this total from another amount that will give me values with decimals of .1 or .2, example: 59-12.1=46.2 not 46.8. The same if 62-8.1=53.2.
What is the formula for taking two times, and finding the minutes between them. E.g. 12:35 PM-12:28 PM=7. I know i can use (A1-A2)*1440, but is there an easier way?
View 4 Replies View RelatedLets say I have a #2 in cell A1 and #4 in cell B1 and would like an output in C1 as "plus 2".
Right now I have =if(a1>b1,b1-a1,???) what can I put in the [value_if_false] to return "plus 2" or do I have to use another formula?
I've got a list of marks (col T), and I want to create a new column subtracting 5 from each value.
Obviously, the formula in this new column is
=T1-5
=T2-5
etc...
But I enter that formula and excel doesn't do any calculating, it just displays "T1-5"
It even seems to recognize that it's a formula, highlighting T1 in the formula and the T1 cell itself, but no resulting value...
I've tried changing the format of the cells (in both columns) to Number and back to General again, but without success.
I've also tried copying and pasting (values only) the whole T column to another column, and tried there, also without any success.
If I wish to amend the below code such that it must use the cell one row above minus the cell on the left, how should I go about it?
Eg,
If in cell C10, the formula is to use C9 minus B10.
If in cell C11, the formula is to use C10 minus B11.
mylogoff.Offset(0, 1).FormulaR1C1 = "=RC[-1]-RC[-2]
I'm looking for a formula that acts the same way as the subtotal formula (revises if rows are hidden and only adds visible rows). Is there anyway to get this removal of hidden rows but for subtraction?
View 3 Replies View RelatedWhat is the simple formula to be used to subract N months from MM.YYYY format
ex:1
Here N=2, MM=04,YYYY=2014
04.2014 minus 2 month means output should be 02.2014
ex:2
02.2014 minus 2 month means output should be 12.2013
i need formula that will look in Col A and perform a subtraction in Cell b1 between 2 value dates 28/12/08 -12/12/08. Though I could have done this manually but was wondering if there is formula which will avoid duplicate dates. So the formula is set and when i copy new data in Col A daily the formula should be able to pick 2 dates regardless where the second date starts at...
View 9 Replies View RelatedIs thee any formula to deduct the higher value from a lower value without carrying. for example:
A1 = 01234
A2 = 56789
A3= should be = 55555
is there any formula like this
Im using the following formula
=IF(E5"",NETWORKDAYS(E5,IF(F5="",TODAY(),F5),Holidays),"")
to calculate training shifts spent in my training centre. We have 2 sets of occupational groups, those of underground & those of surface. I need to subtract 1 shift from the formula but only for the underground guys.
Is there a " Sum" function that can be used for a cumulative series of subtractions?
For example, if I have 10, 12 and 15 in 3 respective cells, and use the sum function upon them, it will return 37.
However, I want to say 10 minus 12 minus 15, and thus return -17.
cell A1 contains the numeric value: 3961.3
cell A2 contains the follwing: ="z equals "&B3-3933.7
which i would expect to give me: "z equals 27.6"
BUT, it gives me: "z equals 27.600000004"
which is awefully strange becos the 4 comes form nowhere...
what makes some sort of sense is that if the value in A1 is changed to 3961.4 or 3961.5, then the final decimal 4 in A2 changes into a 3, and then a 2.
when the value in A1 is changed to xxxx.7 the number in cell A2 becomes what one would expect it to be.
how do i get rid of these strange decimals???
(formatting cells, setting number of significant figures does not work. i naturally already tried that)
I would like if active row cell "AF" has the words "Visa Signature, the same active row "AP" value, 25 should be subtracted from it.
View 3 Replies View RelatedI have Time Data in one column, and Values in the next, and then a blank column (for calculations, like subtraction/time).
Is there any way to automate the =Cell2-Cell1 command for regions of blue cells and have it paste the value in the blank column lined up with the first blue cell of the region?
The attachment will make more sense, but this is what I'm talking about:
Before subtraction macro:
BlueTime1 Value Blank BlueTime4 Value Blank
BlueTime2 Value Blank BlueTime5 Value Blank
BlueTime3 Value Blank BlueTime6 Value Blank
Blank Blank Blank Blank Blank Blank
BlueTime7 Value Blank BlueTime10 Value Blank
BlueTime8 Value Blank BlueTime11 Value Blank
BlueTime9 Value Blank BlueTime12 Value Blank
After subtraction macro:
BlueTime1 Value BlueTime3-BT1 BlueTime4 Value BlueTime6-BT4
BlueTime2 Value Blank BlueTime5 Value Blank
BlueTime3 Value Blank BlueTime6 Value Blank
Blank Blank Blank Blank Blank Blank
BlueTime7 Value BlueTime9-BT7 BlueTime10 Value BlueTime12-BT10
BlueTime8 Value Blank BlueTime11 Value Blank
BlueTime9 Value Blank BlueTime12 Value Blank
I am trying to use a IF statement to extract date data by months.
e.g. : =IF(I13134="A",H13134(Date field) - (date by 3 months)
I hope that makes sense:
So I guess if the field I13134 has the letter "A" in it - Look at field H13134 and subtract that date by 3 months.
Included is an example of a spreadsheet I am working on. There are multiple choices within several different drop-down menu's. As of right now I have the 1st menu as the stage of completion of a car. Within the next few menu's are options.
If welded chassis is chosen, none of these options are included. However if roller or turn-key are chosen then some of these options are included. But then there are also upgrades to these parts that are included as well. Is there a way to make 1 option included when a roller is chosen, but then if you want the 2nd option in the menu, you click on it and it automatically updates the price next to it, therefore subtracting the cost of option 1 from the cost of option 2?
was wondering if it was possible to mark a text or cell as red every time a subtraction was made and also to show the total cell or text as red whenever a link is made ?
View 3 Replies View RelatedTake a single cell in column D, and multiply it by a single cell in column E, which will equal F. Take column F, and multiply it by .02 (2%), which will equal G. Take a cell in column G, and subtract it from F, which will equal I. And this all takes place in the same row. Then have it move down to the next row, and do the same thing..... so it would basically look like this.....
A B C D E F G H I
1 D1 E1 (D1*E1) (F1*.02) (G1-F1)
2 D2 E2 (D2*E2) (F2*.02) (G2-F2)
3 D3 E3 (D3*E3) (F3*.02) (G3-F3)
For easier reading.... in each row I want it to do the following math
D*E=F
F*.02=G
G-F=I
And then do it for every row that I have data in (excluding the VERY first row). I am -COMPLETELY- sorry if I broke any rules, and am also sorry for the poor representation
I am creating a range of numbers from 1 to 2000 with increments of 0.5. Then I have to subtract certain range, for example, 100 to 350. So, the result should display, 1 to 95.5 and 350.5 to 2000.
View 4 Replies View RelatedThis time I have a situation like this:
Column A - Dates
Column B - Equipment Type
Column C - Load Start Time
Column D - Load End Time
I need to do some statistical analysis (average, mode, medium, st dev) on the load times, which is load start time - load end time. As far as I know, I can't subtract ranges (column d i column c) within Averageifs function. I assume I would need to use an array function for this.
I have a data that looks like:
1
a
data_1a
1
b
data_1b
1
c
data_1c
[code].....
Is there a way to automatically make all possible combinations of deduction between members of group 1 only then of group 2 only etc? So it would look like:
data_1a-data_1b
data_1a-data_1c
data_1b-data_1c
data_2a-data_2b
data_2a-data_2c
data_2b-data_2c
Combinations between letter indicators but not between number indicators. BTW groups are different in size starting from 2 till 8 members.
What I would like to do is do a summary sheet that does a little math. I can make it add but only one row at a time. Here is the general idea...
The fields of the summary sheet are 'PromoCode', 'AdvertisingCosts', 'Labor' for example. And I want to add the currency fields (add, as in math addition) of 'AdvertisingCosts' and 'Labor'.
The fields are on another sheet. What I am hoping can be done is that all those fields for every row can be made to work without having to hard code each one.
Also I would like to have it update with any NEW rows that arise. Like if the existing rows were 'row1' and 'row2' and then some other time 'row3' is added that 'row3' automatically gets included on the summary sheet.
I am attempting to perform a cumulative subtraction function that starts over again when the value in a column changes. I have a list that is sorted by a column for item number. The other two columns show the amount we have in stock and the amount being ordered against that stock. The order of rows IS relevant and cannot be changed.
Sample output is attached, with the "given" data outlined in a bold border. The only way I have figured out to do this is to nest a whole lot of IF statements in a formula, which is not very elegant or easy to do, and is limited by the number of IF statements that can be nested.
Formula to calculate time allotted minus time used and show the difference in hour and minute.
View 1 Replies View RelatedI have a problem here:
Eg.
A B C D E F G H I
1 8 pm 9 pm 10 pm 11 pm 12 am 1 am 2 am
2 8 pm 11 pm
3 8 pm 2 am
I typed a formula : =if(and(c$1>=$a2,d$1
when i input the arrival time of A Car, the time he arrives late appears in column D it should read 15 Minutes. how i can get this time to appear automatic after i have registered the arrival time. A Car due at 19:00 arrived at 19:15. 15minutes late. I would be grateful if you could lead me in the right direction.............
View 3 Replies View RelatedI am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the
Private Sub UserForm_Initialize()
If Not Range("dDate").Value = "" Then
TextBox2.Value = Range("dDate").Value
TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM")
Else
TextBox2.Value = ""
TextBox2.SetFocus
End If
End Sub
"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?