How Can I Correct This Time Formula
Nov 4, 2005
How can I correct this Time formula which says in the Tag that it produces a
negative Result and hence displays in cell as ####
=(INDEX($A18:$A33,MATCH(MIN(C18:C33),C18:C33,0)))-(60/1440)
What I am doing is selecting the field in A which equates to the lowest
value in C (C= hourly sales), then I am subtracting 60 mins from the value
selected in A. This in effect will give me my closing Times i.e. find the
cell with Zero Sales then subtract 1 hour to find what must be the closing
time (assuming of course that there is at least ?1 of sales per hour while
open).
My cells in A are formatted as h:mm AM/PM, as is the format in the formula
cell
View 23 Replies
ADVERTISEMENT
Mar 5, 2014
Column A = persons name
Column B = number of hours
Column C = trade
Column D = shift (as in day, night or weekend)
Column E = I would like the formula that looks at the trade and time and then puts the correct rate.
I have a little table on the site with trades/times and rates. IF and IF THEN do something
I took a screenshot to show: [URL] ..........
View 3 Replies
View Related
Feb 15, 2010
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
View 4 Replies
View Related
Jul 25, 2014
I have a row of cells, and some of which are displaying time, but in an incorrect format.
for example 15:20 is showing in a cell as 3.2
How can I create a new cells that basically converts 3.2 into 24 hr hh:mm ....e.g. 15:20?
If I got to use the format cells option, and hh:mm, it changes the cell to the completely wrong time (i.e 04:48) ?
p.s. I should state that "some" of the cells are kind of showing the correct time, albeit with a decimal instead of a colon, for example, cell A12 is showing 4.25
one other thing I should mention.....the times will always be PM...... so 3.2 should be 15:20, and not 03:20
View 2 Replies
View Related
Nov 22, 2008
I have a file which ask for UserId and password. If password is correct, the time stamp should be recorded on the worksheet. However I have some slight probelms on the code I am working on. can someone help me check my coding?
View 14 Replies
View Related
Apr 11, 2012
I have done a VBA course but am struggling to apply it to my situation. What I want to do is on one spreadsheet, I have a column with "shift, date, grade 1, grade 3, quarantine" in it, and in the cells next to it I want an operator to enter either "day" or "night" next to shift, and the date. Next to the others I want the total weight of the appropriate product made in that time. The spreadsheet I will be feeding off is quite big with a lot of information in it. Down the left hand side is the roll number (we make rollstock). Further along on that same row will be the date, the weight, and a grading (putting it into grade 1, 3, quarantine etc). Our shifts run from 7am to 6:59pm, and 7pm to 6:59am. Which shift the roll comes under is determined by it's end time (also in that row). So basically, I need VBA to say sum up all of column [weights] if column [date] = [date cell in working spreadsheet] and if working spreadsheet says day then column [roll end time] =7:00-18:59 and if working spreadsheet says night then column [roll end time] =19:00-6:59 and also if column [grade] = [grade cell in working spreadsheet] ....
Oh, also on the feed spreadsheet i have a column that i put in a little code date, "AP04" for 4th of april kind of thing, rolls being made before 6:59am on the 5th of april being put into the 5th april as they need to be counted under 4th april as they are 4th april night shift (even though the date will stay 5th april)...
View 3 Replies
View Related
Aug 20, 2008
B. start time C. end time D. Total time (military time)
Example: 2300 - 0300 = 4 hrs worked.
=IF(C19-B19>0,C19-B19,1+C19-B19)
I have a row for every date the hors is worked.
I cant seem to get the total hours to calculate correctly at the bottom of column D for a total hours worked in a particular pay period. It calculates a total up to 24hrs then reverts to zero. I have tried [h]:mm =SUM(XX:XX) and just about everything else I can think of and cant get it to calculate a correct total over 24 hrs.
View 5 Replies
View Related
Feb 27, 2012
I have two questions:
1. Is there a way cells with formulas cannot be tampered even if they are not locked? So when I enter a data on a cell with formula, the formula won't be erased? The sample below shows that C1 and C2 has formulas but let's say I enter a value on C1, the formula should still stay? Is this possible?
A B C
1 5 10 =SUM(A1:B10)
2 2 4 =SUM(A2:B2)
2. Let's say I have a total of 125 (A1) and I need to get the answer (B1) where it has 5 with No. 3 on top (subscript) of 5? How to create a formula for this (I hope I make sense)?
View 1 Replies
View Related
Jan 26, 2014
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
#####################
149 entries
Column A - Date - OK
Column B - Customer_Phone - Errors (Should be 11 digits)
Row 21 - Customer_Phone - Error (Not 11 digits)
Row 108 - Customer_Phone - Error (Contains letters)
Column C - Outcome - OK
Please correct and re-check.
#####################
View 4 Replies
View Related
Jul 3, 2008
I have a table with 3 columns of dates and then a column with Set # that I
feel in the box #.
I need to see how many items processed for each set per day.
Example:
[url]
The problem is that it counts the correct amount but not with the correct
dates.
The formula that I use is:
=SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
View 14 Replies
View Related
Aug 5, 2014
correct formula related to dates;
Assume cell A1 contains a date in DD-MMM-YYYY format. I would like to have a formula that looks in A1 and compares to the current date (TODAYS Date) and if the value in A1 is todays date or greater return "YES" and if not return "No".
View 2 Replies
View Related
Feb 25, 2014
Cell A3 on my SHEET1 has the following formula =DATE(YEAR(C1),MONTH(C1) + 1,DAY(C1). It goes on like this for the next 12 months (+2, +3, +4....).
Column B on my SHEET2 has One of the five product codes (A, B, C, D, E) and Column D on my SHEET2 has manually entered dates
I created the following condition for each product code under each month in SHEET1
=IF(MONTH(Sheet2!$D$1:$D$39)=MONTH(Sheet1!$A$3),COUNTIF(SHEET2!$B$2:$B$39,"=A"),"")
The formula only works for two product codes (A and B), and then yields blank cells.
View 5 Replies
View Related
Mar 24, 2014
I have a fomula field in row 67 and 68 .Row 67 calculates the average and row 68 calculates the improvement i.e. average(row 68)- last row inserted(row 67). The values for improvements are not showing correct value. C68 seems to give correct value but B68 and D68 is giving wrong value.
B68=4.8-4.5 =0.3 but it shows 0.4
C68=14.9-15.1=-0.2
D68=11.3-10.2=1.1 but it shows 1.0
I have attached the sheet also.
Sheet 1.xlsx
View 11 Replies
View Related
Nov 3, 2008
I have a column with numbers ranging from -5 to 250. I want to write a formula to correct the numbers in the next column. If the number is less than 0, I want to replace it with 0,if the number is greater than 150 i want to replace it with 150 and if the number is in the 0-150 range it should be kept the same. Is there a way to use inequalities in a formula to do this??
View 2 Replies
View Related
Mar 11, 2013
I am using this formula for long now
=SUMPRODUCT(('[Diesel Statement.xlsx]Nov 12'!$C$2:$C$251=$H$1)*'[Diesel Statement.xlsx]Nov 12'!$G$2:$G$251)
every month i just add a new sheet and change the month name in the formula to that eg
I changed it to
=SUMPRODUCT(('[Diesel Statement.xlsx]Dec 12'!$C$2:$C$251=$H$1)*'[Diesel Statement.xlsx]Dec 12'!$G$2:$G$251)
but now it is not calculating the answer is coming as #value!.
Then I tried to add up by
=VLOOKUP($H$1,'[Diesel Statement.xlsx]Dec 12'!$C$2:$G$215,5,TRUE)
but it does not add all the entries only one entry is shown.
I want the total of the column G. looking up for values in C = H1 value
View 7 Replies
View Related
Mar 24, 2014
I have a fomula field in row 67 and 68 .Row 67 calculates the average and row 68 calculates the improvement i.e. average(row 68)- last row inserted(row 67). The values for improvements are not showing correct value. C68 seems to give correct value but B68 and D68 is giving wrong value.
B68=4.8-4.5 =0.3 but it shows 0.4
C68=14.9-15.1=-0.2
D68=11.3-10.2=1.1 but it shows 1.0
I have attached the sheet also.
Sheet 1.xlsx - Speedy Share - upload your files here
View 2 Replies
View Related
Mar 18, 2008
Excel 2003 SP2
--------------
I have been programmatically setting the formulae in a worksheet. I have various formulae:
Example 1 - =COUNTIF(Data!M$6:M$9871,3)/$E$4
The cell is formatted Percentage, 2 decimal places.
Using the formula evaluator the value returned is 56.80%
The cell displays 56.80%, all is good!
Example 2 - =SUM(IF(Data!$J$6:$J$9871=$D8,IF(Data!$M$6:$M$9871=3,1,0)))/$E$8
The cell is formatted Percentage, 2 decimal places.
Using the formula evaluator the value returned is 53.37%
The cell displays 0.00% !?!
View 9 Replies
View Related
Mar 4, 2014
How to get correct value using vlookup formula in duplicate look up values.
Here i mentioned a eg;
VlookUp_DuplicateValue.JPG
View 8 Replies
View Related
Dec 17, 2009
I have a what I believe to be a consistant formula on lines S12-S18 for gathering the sumproducts for dates on column O. Only problem is that the sumproduct formula is not working on line S18 correctly. I keep looking it over and I can't figure out why it is not returning the correct amount for that cell.
Here is the formula I am using:
=SUMPRODUCT((TEXT($O$12:$O$500,"dddd")=R18)*1)
Please see attached line S18 for the error I am getting.
View 9 Replies
View Related
Mar 27, 2009
Attached you will find the spreadshhet in question. If you click on the click here button input the following info:
8.0
3508949
80
0
0
25
the sheet will auto populate all numbers. this part is working properly. MY issue is if you look at the Bad Debt number, the correct response should in cell f21 should be $636.46 because in order to hit the next level of pay, you must at least hit $3,424,082.00. I can not figure out how to get the correct formula that will put the right pay out. Basically in order to hit a certain level of payout you must hit that next number, even if you are a $1 higher you can not get the pay out. So for example if your bad debt is $3,424,083 you would be paid out at 100%, not 120%.
View 3 Replies
View Related
Dec 8, 2011
I want to create a Macro that uses IF statements to enter the CORRECT VALUE into COLUMN “Q” in the ACTIVE WORKSHEET. I am providing an example of what the data set looks like at the very bottom of this post. I want to use a Macro as oppose to Formula in the worksheet because I want to turn the Macro into an Excel Add-In.
I want the Macro to do the following THREE THINGS:
1. IF the Value in COLUMN L is “0” THEN enter “n/a” into COLUMN “Q”
2. IF the Value in COLUMN L is “2” THEN enter “n/a” into COLUMN “Q”
3. IF the Value in COLUMN L is “1” THEN use a formula that looks like this:
= O8 + ( ( O8 / P8 ) * (First SUM the HOURS in COLUMN O for all the ROWS that have the SAME VALUES in COLUMN C and H and a “2” in COLUMN L and then MULTIPLY that Result against those rows with a “1” in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours). The RESULT of the FORMULA would be entered into COLUMN “Q”. Once you look at the example at the bottom of this post it will definitely start making sense. I highlighted rows 8 – 11 for you in red in the data set at the bottom of this post just to center the attention on the rows I am talking about.
Here is an example of how the formula will work:
I will use ROW 8 from the data set below as an example. From the dataset I know CELL O8 equals 10 and CELL P8 equals 76. Now I will SUM the HOURS in COLUMN O ROWS 10 and 11 for ALL ROWS that have the SAME VALUES in COLUMN C and H in this case the values are “Times” and “PM” and contain a “2” in COLUMN L which happens to equal 3, then that 3 should only apply to those rows with a “1” in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours. So now I plug that into the formula: =10+((10/76)*(3)) and MY RESULT which will go into COLUMN “Q” IS 10.395.
Here the DATA SET:
Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K Col L Col M Col N Col O Col P Col Q Col R Col S Col T
Row 1 B ID Name Org Div T Number Model Make S Function E Function Type Description P ID OG Hours Sum EA P Hours Sum P S Hours EQ
This is what is happening in Column Q Explanation of Formula
Row 2
Times
Cont
2
28 28 n/a
n/a If formula populates Column Q cells with "n/a" if Column L cells contain a "0" or "2".
Row 3
Times
Cont
1
404 1194 413.4740369
O3+((O3/P3)*28) You get the Sum "28" by adding all the "P Hours" in Column O that match these 3 values:
1. The "Org" value of "Times" in Column C
2. The "S Function" value "Cont" in Column H
3. The "P ID" value "2" in Column L
[Code] ...........
View 9 Replies
View Related
May 18, 2012
Users copy and paste source data from a report into worksheet 1 each month. Data from last month is deleted and data for the current month copied into worksheet 1.
I am trying to write a formula within worksheet 1 to check that data for the current reporting period only is in worksheet 1. For example all data from last month's reporting period has been removed and the only data in worksheet 1 is the current reporting period.
Reporting period is shown in two columns Year and Period number (1 to 12).
View 4 Replies
View Related
Jun 27, 2014
How to make a formula with references to certain cells, so I can sort the sheet but keep the formula referring to the same cells as before the sorting?
In the example I illustrated the problem: when I sort the data, the formula refers to the same cells which have now different values, so the calculation is not right anymore.
View 1 Replies
View Related
Mar 27, 2014
I'm trying to find the correct formula to sum text that meets two criteria. If C2:C1023 is Equal to TEXT from J8 and F2:F1023 is equal to TEXT J4 add up the total times J4 shows up in C2:1023
=SUMPRODUCT(Sheet3!C2:C1023=J8,Sheet3!F2:F1023=J4,Sheet3!C2:C1023)
View 11 Replies
View Related
Feb 24, 2014
I have the following code, which executes just fine:
[Code].....
The problem is that I need to run this again in a different column, and the data is held in another sheet that I have given the variable StepTwo to.
I'm not sure of the syntax to do this. Basically - this is what I need the code to do:
[Code] ....
Understandably this is totally incorrect, but it's the best I can do to explain what I'm actually trying to do....
The two parts of SUMIF are both held in a workbook that I've given the variable "StepTwo" to. Both sets of data are also held on Sheet(1) of that workbook.
View 7 Replies
View Related
Aug 8, 2014
I am trying to save some time converting a cut up inventory catalog number. Specifically focusing on the color code. I have the list of colors associated with their specific part number. The order cannot change or my sheet will mismatch.
What I want to do is convert the # in column A to the color code text in column b so that it matches the translated code in column C? For example
1 I want to tell 1 to = Polished Chrome anytime #1 appears in my column.
What formula should I choose to achieve that goal. As well, how do I know what data to imput into the formula wizard?
Sheet is attached.
I have Cut a few thousand items off the bottom of the sheet. I can apply to additional data left off.
View 4 Replies
View Related
Jun 24, 2009
I'm trying to create a formula in cell f13 of my attached spreadsheet "Sample 1" that will search the 2nd attached spreadsheet "Sample 2" and return the correct serial number based on both the matching PO # (located in cell E10 on Sample Sheet 1 and in Column 5 on Sample Sheet 2) and Product # (cell A13 on my Sample Sheet 1). My current formula is not returning the correct result and I'm not sure why.
View 4 Replies
View Related
Apr 27, 2014
Formula to calculate time allotted minus time used and show the difference in hour and minute.
View 1 Replies
View Related
Jul 1, 2012
I 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
View 4 Replies
View Related
Feb 18, 2007
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 Related
