i have a due date column and an adjusted due date column. a proof is allowed to be held 3 days, if it is held longer, the due date is adjusted. for every day over the allowed days that a proof is held, we are allowed 2 more days of production. i want to create a formula that will calculate how many EXTRA days the proof was held, multiply that by 2 and then add that many days to give us the adjusted due date. is there a way to do that?
I have the output of my database in an excel file and one of the columns is the date of instalation (of a PC in this case) and I am trying to use the date of instalation to calculate the date of RENEWAL (we have a set life cycle for our machines and so we calculate in months when its due for an upgrade).
I think just a simple formula will work. I have attached an excel file with some example information.
In column B I have the computer type(this will determine how long of a life it has), In column D we have the instalation date which is the starting date (the start of its life cycle). Here are the variables.
I'm using a few worksheets to make balance sheets. I use a connection to a database. Worksheet1 contains the opening balance amounts per account. Worksheet2 has a balance sheet. Because the database contains more than one administration I use the sumproduct function so I can use multiple criteria. The formula I use is: =SUMPRODUCT((Worksheet1!A$1:A$4000=Balance1!$A$2)*(Worksheet1!C$1:C$4000=Balance1!A4)*(Worksheet1!G$ 1:G$4000))
One of the administrations has 3 new accountnumbers. These were added to the database. When I refresh the data Excel adjusts the formula and adds 3 rows to the ranges, except the last range. The formula then looks like this: =SUMPRODUCT((Worksheet1!A$1:A$4003=Balance1!$A$2)*(Worksheet1!C$1:C$4003=Balance1!A4)*(Worksheet1!G$1:G$4000))
Because the last part (Worksheet1!G$1:G$4000) is not adjusted, Excel returns a #N/A error. What is wrong with this formula? It works great as long as no accountnumbers are added to the database.
I have 2 columns of dates and would like to find the difference in 2 dates in column a and b for example. when I use the formula a2-b2 I get #Value! or # Name? in these cells. The cells appear to be in format dd/mm/yyyy. I tried formatting them to mm/dd/yyyy, but the dates are not changing at all. I have quite a few of these spreadsheets so it won't be practical to retype each cell individually.
Here is a table with values from Factor1 to Factor 5. Underneath that, for a specific index, I can mark Y or N for factor values. For instance, for index 1, Factor1 and 2 is marked as Y. The goal is to calculate total factor based on variable and factor values. In this case, variable value is 6, factor 1 =1 and factor 2=1.25). So Total factor = 6*1.25*1.5 = 11.25. If all factor values are marked as N, then total factor = variable value (Example is for Index 3)
How this can be implemented. I tried using COUNT function to count the total number of Y but that works only if all factors have same values.
I have two date columns named "start_date" and "Dead_line".
Both the columns are in the date format. Ex: 9/14/13 9:14 AM
i want the third column to be the "status" column.
if sysdate-dead_line < 30 % of (dead_line - start_date), then the status column should become green. if sysdate-dead_line is between 30 % and 70 % of (dead_line - start_date), then the status column should become amber. if sysdate-dead_line > 70% of (dead_line - start_date), then the status column should become red.
First the dead_line - start_date to be converted to hours, then have to do conditional formatting like mentioned above.
I need to work out Revenue Rank & Year to Date calculations.
Consider a simple table:
| Partner Name | Year | Month | Revenue | ------------------------------------------ | John Smith | 2008 | Nov | 2000 | | John Smith | 2008 | Dec | 2200 | | John Smith | 2009 | Jan | 1898 | | Mary Smith | 2008 | Nov | 1767 | | Mary Smith | 2008 | Dec | 1867 | | Mary Smith | 2009 | Jan | 1953 | ------------------------------------------ etc..etc...
I'm not using Pivot Tables since there are more complex issues around presentation which are preventing me doing this so are using good old formulas..
Revenue Rank is in reference to the Partner in this case. I need to be able to say John Smith is rank x out of xx by summing up his revenues for both:
a) one month b) a range of 3 months back
How do I work this out? Especially the date calculations when I just have a year and month in separate fields?
I am having problems with calculations to be performed on date format. As in attached excel sheet, Start Date and End Date can be defined by the user. Once the user enters the dates, year 0 onwards are to be populated with values so that: (Also, not sure if I will need a button to initiate the calculations or a direct function will suffice)
year0 start = Start Date, year0 end = (Start Date +12 months) or (End Date), if End Date is before (StartDate + 12)
year1 start = year0 end, year1 end = (year0 end + 12 months) or (End Date), if End Date is before (year0 end +12)
year2 start = year1 end, year2 end = (year1 end + 12 months) or (End Date), if End Date is before (year1 end +12)
year3 start = year2 end, year3 end = (year2 end + 12 months) or (End Date), if End Date is before (year2 end +12)
year4 start = year3 end, year4 end = (year3 end + 12 months) or (End Date), if End Date is before (year3 end +12)
year5 start = year4 end, year5 end = End Date
If the dates are such that all years 0 to year 5 may not be covered, the years not used should be blank.
look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them. note some are on the left and some on the right.
The attached workbook has dates in column C, although some of these dates are just strings.
I'm trying to write some vba that will tell me how many of the cells in column C contain a date (or looks like a date) that is greater than (after) the real date in cell G1.
At the moment I loop through the cells in column C and can ascertain, which dates can be counted, then copy one row over at a time, but I'm looking for a slicker (perhaps one-liner) answer, perhaps by copying a block of rows in one go. The aim is to copy those rows to another sheet. There are many more rows than in the attached, and many sheets to process, and I have no control over the format of the dates/strings in column C. Currently it takes about 20 seconds to copy over the necessary rows, but I'm looking for it to happen much more quickly; current thoughts are to sort on column C (sorting on column C anything that looks like a number as a number - which has it's own problems!), have a count of dates satisfying the criterion (say using a worksheet formula such as COUNTIF or SUMPRODUCT, perhaps also using EVALUATE) then copy a block of rows in one go.
not very relevant, but the existing code is something like this which highlights rather than copyies the rows(included in the attached): ...
i'm trying to use HLookup to find an adjusted midterm grade that's given. but i have some conditions:
If student missed exam and has a zero – keep zero. If student has a grade of 1-119 points, increase their grade 40 points. If student has a grade of 120-125 points, increase their grade 35 points. If student has a grade of 126-131 points, increase their grade 31 points. If student has a grade of 132-139 points, increase their grade 27 points.
with these conditions, if my midterms grade is 120, how would i calculate it using HLookup? i worked on it but i keep getting the #NA! error. =H4+HLOOKUP(H4,B24:D25,2).
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.
To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet
I'm using Excel 2010. I need to populate a daily calendar with the number of nights spent, extracted from the Date of Arrival and Date of Departure of individuals.
In the expense log, Column C is a list of Dates and Column I is a list of expenses. I want to Sum the expenses in the 'Expense Log 09' to a new sheet based on a Date entered in H24 on the new sheet. I have tried the formula as shown below and Get the result #NAME?
=SUM(IF(Expense Log 'Expenses Log 09'!C8:C100,H24,'Expenses Log 09'!I8:I100)). I would Like to SUM all expenses After the posted date including that date.
i am trying to see if a date is between 2 dates. i have a sql dump and want to create additional colums before creating a pivot. each line was created with a date and i want to create 2 new colums financail year, month. not sure if it is possible with vlookup and that is about as advanced as i get. attached is a summary table.
I have a variable start date in cell A1 and end date in cell B1. Lets say, start = 04/01/07 in A1 / end = 3/31/10 in B1 (date format used = mm/dd/yy)
I am looking for a way to partition these out in subsequent columns as follows:
A2 and B2 would = 04/01/07 and 12/31/07 (this section ends at the end of the first year specified in cells A1 and B1).
A3 and B3 = 01/01/08 and 12/31/08 respectively
A4 and B4 = 01/01/09 and 12/31/09
A5 and B5 = 1/1/10 and 3/31/10
I would like to be able to enter any start/end date values into cells A1/B1 and have the cells directly underneath split these dates out chronologically as described above.
The number of populated cells in rows underneath would need to grow dynamically based on dates input in cells A1/B1.
I have been chatting up mr.excel.com to many coworkers recently. Excel is becoming a big deal at my office. I hope to see them chekc this site out.
I have this script it does exactly as i want it to do, filter a table of date using two selected date in D3 and D4. There is only one problem: when i choose the dates and run the script it changes them around it around. e.g cell D3 = 01/11/2006 cell D4 = 01/12/2006 so it should just show all of november (11) but D3 will filter using 11/01/2006 and D4 will use 12/01/2006
I have a formula that tries to determine if a date is between two dates. The formula I have is =IF(AND(A1>=A2,A1<=A3),"yes","no")
a1 = 7/31/2009 a2 = 8/1/2008 a3 = 3/31/2010
If the date is between those two dates then "yes", if not then "no". I tried this formula a couple times and it works when a3 is 3/31/2010 but when I try 3/31/2009 for example it does not work. So it seems like there is something wrong with the second date I am referencing.
formula to input to excel to get the dates rolling down in A cell i want it to go A1(1/1/2014) A2(1/1/2014) then a3(2/1/2014) a4(2/1/2014) a5(3/1/2014) a6(3/1/2014) i have a lot of these dates to put into excel
I have a spreadsheet with reference relating to dates that are listed as single days. I am trying to convert the single dates relating to a reference to a from and to date but i'm having problems.
I have a spreadsheet that lists items in column A with a start date in column B. What I need to do is in in column C list the next review date in increments of 5 days, but I only want the next day to show from the current date, not the inputted start date.
For example:
Column A Column B Column C Item Start Date Next Review Date Item A 12-Sept-09 27-Sep-09
Can I get a formula to calculate the increments and only show the next calendar date for the review, and then to change to the next date once the previous date has passed.
Alternatively, I have another sheet in my workbook that calculates the dates, is there some way I can display the next future date in a line of dates?
I have a cell pulling the date from another page and the dates comes across as "2/1/2013- 4/15/2013"in cell B2 and I have the code in sheet 1[code]=IF(OR(Safeway!A43=""),"",Safeway!A43)[code] pulling data from the sheet 2 already in the middle with a code for later use to hide a certain character. I have a condition code set so if the date is past current date, it will turn red but with the 2 dates in the same cell, I am unable to get it to turn red. How do I go about only having the 2nd date show up so the condition works while already having this one code in the cell? I attempted to add the early stages of this program for review.
I need to set a format in which if I enter a num eg 15 on the Jan Column, format comes as "15-Jan" format or If i type 20 on the march column , it comes as "20-Mar" format
I would need it in Date format, because I would be comparing dates later on.
How to change the date automatically depending on the instructions. If the date of the medical certificate of the Government (MCG) included, automatically private medical certificate (MCP) date will change to the rear on the day before the Government's medical certificate given if the date overlaps
i have messed up column A which has only dates and i have different date formats like 03/07/2013 and 07/03/2013 i need to have a fixed date for all in B like 03 July 2013
