Negative Times
Dec 22, 2009
For simplicity sake I will put what I have in close proximity cells and what my issue is. I am taking a number A1 (7.7) and turning it into time A2 =A1/24 (7:42)
A3 (18:00) Which is our work start time. I am taking 7:42 min estimated work day hours and adding that to our start time of 18:00 for A4.
A4 =A2+A3 (1:42) This tells me that we should get done around 1:42 am
A5 I enter the actual time we finished. Let's say (2:23)
A6 =TEXT(MAX($A$4:$A$5)-MIN($A$4:$A$5),"-H::MM")
This gives me an answer of (23:19), but if I type over the formula in A4 (1:42) which is the answer to the formula and already has that number there, I get the answer (0:41) in A6 and that is the answer I want. I can't figure out why I can't get A6 to give me an answer of (0:41) with a formula in A4. I even tried having another cell formulate A4 and then A4 =that cell and it is still the same.
View 5 Replies
ADVERTISEMENT
Jan 5, 2009
I am doing math on times and want to be able to SEE values of Negative times instead of the ########. This is happening in a time card because I am summing the weeks total and subtracting 20 from it (expected 20 hours per week) so it will ALWAYS be negative until the last day of the work week.
View 4 Replies
View Related
Sep 20, 2012
I have a spreadsheet that I have combined the HH/MM/SS into one column and formatted it as time. If the numbers are negative then the - comes with it into the new column. I want to create a new column to calculate the negative numbers as zero and leave the positve numbers as they are.
Here is the formula that I used to combine the columns:
=TEXT(O322,"0:")&TEXT(P322,"00:")&TEXT(Q322,"00")
View 9 Replies
View Related
Jan 10, 2013
I have a problem with my spreadsheet where i have needed to show over and under runs of theaters. So I have positive and negative times - which i solved using the 1904 date system calucaltion in the options menu.
But since changing this all my dates (in a seperate column) have increased 4 years and 1 day so the 3rd December 2012 is now 4th December 2016.
View 1 Replies
View Related
May 12, 2009
I have a large dataset (24000 rows) that requires me to multiply two different columns of integers. In some cases, the two integers are both negative and multiplying them results in a product that is positive. I actually need that product to be negative rather than positive. I can't quite seem to figure out the best way to accomplish this.
View 5 Replies
View Related
Aug 1, 2007
I have data that comes from a subsytem that places the negative sign at the right of the number, so it is recognized as text. I can get around this using find and replace and then a second step to multiply that by -1, but is there a formula that can do this for me?
I was trying if(right(A1,1)="-",TBD,A1)
View 4 Replies
View Related
Sep 1, 2007
I have data starting in E7. I want it to go down the column and find the negative numbers. If it finds one then I want it to change the number in the row to the left of it to a negative. So if E67 is a negative number, make D67 a negative and so forth down the line Sounds "simple" but how do I do it?
View 7 Replies
View Related
Jan 19, 2009
I have data in range J2:J365 , H368:H401 & J403:J827. i want to check wether this range have negative values or not if yes load all negative values in the listbox1 by clicking checkbox.
View 3 Replies
View Related
Feb 23, 2010
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
1:45:
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
View 11 Replies
View Related
Apr 8, 2014
I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:
E3 provides the start time of 4:00
H3 provides an end time of 15:30
If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.
View 7 Replies
View Related
Mar 7, 2009
I need a formula in excel to convert start times and end times and minus out lunch time taken and then give total hours worked. For instance,
Column D Column E Column F Column G
Start End Lunch Total Hours Worked
10:01AM 7:08PM 1:01 (formula to convert hours worked)
View 9 Replies
View Related
Sep 15, 2006
I thought I found a formula that would work, but it's not working. Each month I have to count the number of service tickets that have arrived between certain time ranges. They want to gauge during what times we seem to get the biggest batch of service requests.
6 am to 10 am
10 am to 5 pm
5 pm to 6 pm
6 pm to 6 am
The format of the cells are:
1:21:19 AM
1:28:08 AM
1:35:48 AM
1:49:19 AM
2:17:02 AM
7:14:38 AM
7:29:12 AM
8:08:28 AM
8:51:48 AM
8:54:19 AM
The formula I tried for 10 am to 5 pm: =COUNTIF(B2:B677,">="&TIME(10,0,0))-COUNTIF(B2:B677,">"&TIME(17,0,0))
It gives a result of 676, and I know from manually counting that there is only 327 cells that have a time between 10 am and 5 pm.
View 3 Replies
View Related
Jul 12, 2007
I included a spreadsheet that lays out what I am looking to do, basically I copied some times and speed from net. It shows up in non 24 hour format. I need to find the times and an associated speed for each row (day) directly before and after my inputted desired time. I've searched for days, tried different formulas with index,match, lookups,timevalue, time, etc and even tried using other peoples vba code without success.
View 9 Replies
View Related
Sep 28, 2007
i need to calculate between time. If a person is working between 8 till 12 and then 1 till 5 i need to count the instances between these times.. so if somebody is working between these hours a 1 should appear and if they have finished their shift or are on lunch then this should change to 0
e.g.
8.00 | 8.30 etc.. 12.30 | 13:00
1 | 1 | 0 | 1
View 9 Replies
View Related
May 29, 2014
How can you calculate the present value of a negative value in excel?
View 2 Replies
View Related
Jan 30, 2007
i am tracking my working hours at night, so i type in the time i start and the time i quit like this:
A 1 start B 1 end
A 2 22:30 B 2 02:30
now i want to calculate the time between.
but since excel don't like the negative time i got a problem. i figure i must make a function something like
=IF(B2<A1,B2+24)
i have tried a few but i don't get the parameters right so i get errors,
View 9 Replies
View Related
May 8, 2009
I am trying to show how many years it will take for a retiree to run out of money.
row 1 is his available money (this in determined with other formulas such as income - expenses ect)
Row 2 is number of years (J2 would be 10 years)
Let's say available money turns negative on the 10th year (tenth column "J")
How can I write a condition statement that will say that says if the amount in row one is positive do nothing, but when it turns negative add row 2 of whatever column it turned negative in?
Example:
A B C D E F G H I J K L
5000442538503275270021251550975400-175-750-1325
123456789101112
View 7 Replies
View Related
Jul 7, 2009
I've got a file with sum formulas and datas as well,i need to know when ever i'm getting a negative no as result, it should be zero or the cell should be empty.
View 4 Replies
View Related
Feb 17, 2009
i have for example column I, J & K. col I and J has both negative & positive values and K (I/J) works out the %
Col I Col J col K
135975 583333 23%
-27748 583333 -5%
-18186 583333 -3%
272550 583333 65%
But i need a formula or formatting that always shows the negative percentage as a zero.
View 2 Replies
View Related
May 23, 2012
I need to delete a whole row if column J contains a negative number.
I am having difficulties finding the correct wording for picking up the negative value.
I can get it to delete using a word as the reference but not for negative values
Code:
Sub DeleteRows()
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("j1", ActiveSheet.Range("j65536").End(xlUp))
Do
Set c = SrchRng.Find("
View 4 Replies
View Related
Mar 9, 2013
I would like to add to this formula.... if the result of "$L$6-Q15", from below, is 0 or a negative number, then the desired result would be 0 for this formula.
Possibly wrapping IF AND somewhere in this...
=IF((O15+(K16*$L$5))>=$L$6,$L$6-Q15,K16*$L$5)
View 2 Replies
View Related
Aug 15, 2007
This may sound simple, but is not (it might be easy for you hopefully)...... I have a string of numbers and the first few numbers can be either postive, negative or zero....Later on, the numbers all become negative......
I need to find the the cell which contains the first negative number in the consequtive negative numbers.....The difficult part is the numbers before the consequtive negative numbers can be anything......
View 9 Replies
View Related
Jan 2, 2008
is there a way to format a cell that will make the number a negative number regardless of what number I type; whether I type a positive or negative.
What i'm trying to accomplish is enter a number and the number will be negative. I don't want to have to type a negative in front of the number.
View 9 Replies
View Related
Mar 8, 2008
I have a formula calculating time to the second (e.g HH:MM:SS). I am calculating the difference from the time something was scheduled to be done vs. when it was actually completed. What I can't get Excel to do is calculate the same time but listing the time in negative, as if the project went over and by how much.
I know how to do this in numbers view, but unable to do this in time view.
View 9 Replies
View Related
Mar 10, 2008
I have, on some occassions, negative minutes e.g. -125. I have a formula in a separate column which divides the minutes by 1440.
Hence I could have
AE AF
Session Minutes Session Time
210 03:30
-125 ############
Column AE is General format, whilst column AF is Custom i.e. hh:mm.
How do I rectify the formula AE2/1440 so that I get the Session Time to work but with a negative sign?
View 10 Replies
View Related
Oct 24, 2008
is it possible to display the value in a cell with a £ and also make it automatically go red when the sum is negative?
View 9 Replies
View Related
Nov 5, 2008
How do you format cells so that when a number is entered in a certain cell it will automatically reflect that same number as its negative value, displayed in red & in brackets.
View 9 Replies
View Related
Sep 23, 2009
I'm trying to sum negative numbers using the following formula, however for some reason it's not working in the way that it would if I flipped the < to a > sign.
=SUMPRODUCT((Data!$AB$10:$AB$3222=$C$58)*(Data!$L$10:$L$3222=D57)*(Data!$W$10:$W$3222
View 9 Replies
View Related
Dec 11, 2003
Using Excel 97, Is there a way to Sum the values in columns if the value in cell B is a negative number. I want the total placed in their respected columns after excel finds 8 Consecutive Blank rows.
Ex
B1:B70 (remember the number can change it can also be B1:B80)
B1:B70 is a positive value, but then B85 is negative so begin the calculation from that point.
Sum B85,F85,G85,H85,I85,J85,K85,L85,M85,Q85,R85. And place the value in their corresponding cell after 8 consecutive blank rows appear in row B.
View 9 Replies
View Related
Dec 4, 2009
I am trying to sum up numbers that are in alternate columns but if the number is negative I do not want that one added. I am getting the standard # name error. Here is my formula: =SUM G3+J3+M3+P3+S3+V3+Y3, If >0
View 3 Replies
View Related
