Multiple Condition If Formula That Will Take Into Consideration Month Only In Date
Mar 25, 2014
formula that will compute contract amounts that are paying out for a given month, depending on what is listed as the contract renew date and how often the contract renews.
Assume the contract date is in C1, contract renew date is in C2, Contract term is a drop down menu in C3 that has two options of text, 6 Months or 12 Months. C4 gives the contract amount. There are then dollar amounts in C5 and C6 that are only taken into consideration if they are $0.00. In B1 will be the run date or the date I want to pull my info for.
I basically need a formula in C7 that will return the contract amount from C4 taking into account these conditions: If there is a dollar amount in either C5 or C6, return "$0.00" in C7. But if C5 and C6 are both zero, than: If C3 = "12 Months" and B1 is in the same month as C2 (I don't want it to take into account the year of day of the date), than return the amount listed in C4 in cell C7. Or if C3 = "6 Months", and B1 is in the same month as C2 or if B1 is 6 months ahead of the month listed in C2 (again not taking into account the day or year), than return the amount in C4 in cell C7. So basically if the contract amount in C4 was $100, and B1 3/1/2014 and the contract renew date is C2 is 9/15/2013, I still want $100 to appear in C7, even though there is only technically 5 and a half months in between the dates of 9/15/2013 and 3/1/2014. I want it to look at these dates and add six months to the month of September and then look at cell B1 and as long as that date is a date in March, return the contract amount in cell C7. I also want $100 to appear in C7 if the date in B1 was a September date. In the situation where C3 is set to 12 months, if C2 is a September date than $100 will only appear in C7 if B1 is also a September date.
And one last thing, I'm going to somewhat withdraw my request that the year shouldn't be taken into account. This is only partially true. It just occurred to me that the $100 should only appear provided the date in B1 is in the same year or in a subsequent year AFTER the year of the contract renew date in C2. So for instance if, C2 is 9/15/2013 and I put in 03/01/2013 in B1, it should NOT calculate the $100 in C7 since the contract technically would not have renewed in March of '13 as it didn't exist until Sept of '13.
View 1 Replies
ADVERTISEMENT
May 18, 2009
Please refer to attached Sinking Fund worksheet. have managed to solve most of it. Just need to figure out how to match the periods out. My requirements are on the worksheet.
1. To get the schedule on sheet 1 to stop calculating further once it has reached the actual number of payment periods as shown in H12.
2. To get the interest calculation in sheet2 to stop once the actual number of payment periods as shown in sheet1 H12 is reached.
View 3 Replies
View Related
Mar 20, 2009
I have log data in two columns:
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data
On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.
The end of the range is determined by the month in the current row.
I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.
I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.
Manual
=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536
View 9 Replies
View Related
Jun 2, 2006
I need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.
View 2 Replies
View Related
Feb 27, 2008
I have this problem on putting the right formula. Column A entry are dates corresponding to replacement of parts x at column B with running hours of parts x at column c respectively. the table will look more like this:
A B C D
1 jan 2, 2008 brake front 200 ?
2 jan 20 , 2008 clutch 150
3 jan 30, 2008 wheel front left 300
4 feb 2, 2008 brake front 50
5 feb 5, 2008 brake left 85
6 feb 15, 2008 clutch 300
the formula will first look at column B and look for the entry "brake front" and every time it matches the entry it then looks up the date and then displays the corresponding latest running hour value on column C at cell D1. D1 formula will be having a result of 50 as it is the latest running hour value compared to 200.
View 9 Replies
View Related
Feb 2, 2014
I need cell (O4) to display days overdue or days remaining on an assigned task based on subtracting due date (M4) from todays date, but only perform days subtraction function if (M4) is not blank and only if task complete cell (N4) is less than 100.
Have the conditional blank cell figured out, but nesting another condition for the less than 100 complete cell. So in other words, I don't want cell (O4) to subtract days and display any error, irrelevant data if there is not any due date entered or the task is entered as complete...
View 3 Replies
View Related
Apr 16, 2006
in cell A1 and cell B1 I have a "yes", "no" or "maybe"
in cell C1 and D1 I have a number
if cell A1 and cell B1 "yes" multiply cell D1 times 2 (all results in cell E1)
if cell A1 "yes cell B1 "no" multiply cell D1 times 1.2
if cell A1 "no" cell B1 "yes" multiply cell C1 times 1.2
if cell A1 and B1 'no" multiply cell cell C1 times 2
if cell A1 "maybe" cell B1 "yes" multiply cell D1 times 2
if cell A1 maybe cell B1 "no" multiply cell C1 times 2
if cell A1 "yes" cell B1 "maybe" multiply cell D1 times 2
if cell A1 "no" cell B1 "maybe" multiply cell C1 times 2
View 6 Replies
View Related
Apr 30, 2007
You all were so great last week (thanks Ninja). This is a tracking form for a contest based on restaurant cost results (Page attached). I need a formula for if C2>D2, but less than 1/2%, give100, if C2<D2, give 200.
View 9 Replies
View Related
Jun 29, 2007
I have a worksheet where there are rows of information in a specific range (A5:G600). I'm using the conditional format of =AND(MOD(ROW(),2), COUNTA($A5:$G500)) to alternate the row colors. I would also need to have the information in column A formatted. Column A contains dates, and if the date is 90 days less than or equal to today's date (90 days or more old) I would need to format that also.
I've tried using both at the same time, but the conditional formats will override each other when applicable. Am I able to do this via conditional formats, or would I need to do one or the other in VBA?
View 8 Replies
View Related
Nov 13, 2011
with the formula below..
=SUM((Sheet2!A$4:$A$17475=AM2918)*(Sheet2!B$4:$B$17475=AN2918)
*(Sheet2!H$4:$H$17475="Left")*(Sheet2!$E$4:$E$17475>=AR2918)*(Sheet2!$E$4:$E$17475)
View 8 Replies
View Related
Jan 21, 2010
In the Total column, I would like to determine what the total would be as from the start date till the current date
Columns "C:I" has the dates and the Monthly applicable rates associated.
(in this example, they are annual dates, but it may be that rates change in between a year as well)
In the first set of details (Mr A), the start date is 01/10/2005
Since Mr A only begins 01/10/2005, the rates from 01/07/2004 - 30/06/2005 ($9) would not apply.
However the rates from 01/07/2005 - 30/06/2006 ($8) would be applicable for Mr A for the period 01/10/2005 - 30/06/2006 (ie.9 months) ....
View 13 Replies
View Related
Apr 10, 2007
Is there a formula in excel that will automatically populate a field with the prior months end date? For example - If my spreadsheet is opened on March 25, I would like the date in cell A1 to read 2/28/07.
View 3 Replies
View Related
Dec 23, 2008
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
View 4 Replies
View Related
Mar 28, 2008
we can get if functions to return conditions, as in if so and so "a" etc... is it possible to return long statements, instead of letters, with fill stops and commas. i don't know if it would be appropriate if i put an example of what i was trying to do. i am not trying to be rude but this
will this work in c3
=if(b3=5,"Readily understands classroom statements, questions and instructions.Can usually understand short electronic texts without difficulty.",if(b3=4,"Understands most classroom statements, questions, instructions.Can usually understand short electronic texts with only occasional difficulty.",if(b3=3,"Can generally understand classroom statements, questions and instructions, but may need repetition.Can understand short electronic texts reasonably well.",if(b3=2,"Sometimes misunderstands classroom statements, questions, instructions.Has some difficulty in understanding short electronic texts.",if(b3=1,"Has noticeable difficulty in understanding classroom statements, questions, instructions.Has great difficulty in understanding short electronic texts.")))))
View 9 Replies
View Related
Feb 18, 2004
I need to do an hour calculation on two cells which have dates and times in both. the first cell is a call that we get from a customer and the second is the date and time in which that call is closed by us...meaning that call is complete.
I need to calculate how much time in hours did it take us to complete that call for the customer. I need this calculation to respect our business hours of Monday to Friday 8am-5pm and closed on Saturdays and Sundays.
here are some examples.
from - 2/12/2004 13:00 (thursday)
to - 2/13/2004 9:00 (friday)
answer should be 5 hours
from - 2/13/2004 14:00 (friday)
to - 2/16/2004 10:00 (monday)
answer should be 5 hours
View 9 Replies
View Related
Jun 7, 2009
Can someone tell me what I'm doing wrong for the weekly sums in this spreadsheet? The monthly sums work fine.
PS I can't use pivot tables. This spreadsheet is a quite small part of a more expansive set of worksheets, from which I am pulling data.
View 7 Replies
View Related
May 30, 2014
I uploaded an example file.
Now, what I need to accomplish is that the D1 and D3's in sheet 2 need to result in a date next to the correct country (the date (in full) must be the first monday of the correct week). I find it quit difficult to do this because in sheet 2 you have once the country name, but several possible dates. So in sheet 1 there must be a date for every D1 or D3 but under each other.
The second problem is that I need to accomplish to get a "x" in sheet 3 under the correct month where there is an D1 or D3 in sheet 2 (week).
So I need to go from a week to a month and this can be for one country 1, 2, 3 or even more months (it depends from the D1 and D3's in sheet 2).
View 13 Replies
View Related
Jun 5, 2014
I have a sheet where the columns have the month end date for each date, ex January 31,2014 February 28, 2014, March 31, 2014 etc. These dates are used in another formula to compare to the current date and if the current date is past the result is different from when if the date is in the future.
I would like a formula that would update the Month end date when we enter a new year. So for example once we get to January 1 2015 my date would change to January 31,2015, February 28, 2015, March 31, 2015. this would note be dependent on the system date but on the date in another cell.
View 3 Replies
View Related
Feb 2, 2004
This formula on the forum has helped me to find the first day I specify of the month of a certain year. (here a monday in august of the year specified in B1)
=DATE(B1;8;8-WEEKDAY(DATE(B1;8;1);3)-IF(WEEKDAY(DATE(B1;8;1);3)=0;7;0))
As I'm not really good at understanding the formula (I get confused with the =0;7;0 bit), even though I can make it work I'd like to know how I can find the a last specific day (mo-su) in a month.
View 9 Replies
View Related
Aug 20, 2013
Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.
The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.
View 1 Replies
View Related
Mar 17, 2013
I have workbook with multiple sheets with full of formulas. I need a excel macro which can convert formulas to values based on multiple conditions.
The formulas are in B2 and below.This formula provides results as soon as a value is entered in C2 and one among D2 and E2 cells (see attached excel)I want a excel macro to convert the values provided by formula to actual values as soon as results are obtained.Further, when user delete data in C2, D2 and E2, i want the formula to be working again since if a user enters different data in C2, D2, or E2 the value should get updated.
I found a similar post which addresses some part of my problem but works only with one condition. [URL] ....
View 8 Replies
View Related
Oct 10, 2007
A person spends some time performing various works (work1, work2...) in a day. Each work can be further divided into tasks (task1, task2...).
I need to find the total time spent by the person doing all these activities.
The attached spreadsheet will explain it better.
View 3 Replies
View Related
Dec 30, 2008
i use dates a LOT in my job however if i want to add a month to a date the best i came up with is the following:
BTW all dates are USA standard (m/d/y)
A1 = 12/1/2008
Lets say i want to add 3 months but the day NEEDS to be 1 because of all of my array sumif's are based off of month 12/08, 2/09 etc, and in excel 12/08 actually translates to 12/1/08. So here is the formula I have been using for the last 4 years....
=date(year(a1),month(a1)+3,day(1)
that seems a bit "Clunky" to me anyone have a better idea?
do not tell me "=a1+30" or "31" because that does not work universally.
View 8 Replies
View Related
Apr 4, 2009
I would like to create a financial modeling spreadsheet. Basically my problem is this; I want to have a month by month row for 5 years. For the value of each one I would like it to increase by 950 per month automatically.
View 4 Replies
View Related
Apr 24, 2014
What 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
View 2 Replies
View Related
Feb 22, 2008
I have a worksheet where the current months date is entered in cell E1 (format dd/mm/yyyy) , using an input box.
I need a formula the will calculate the previous months month end date in cell A6 and the month end prior to the date calculated in A6 in cell A13.
where the dates start with 31 for eg 31/01/2008 , then my formula works, but if it starts with 30, 29 or 28, then obviously it does not work.
I would like a formula that will calculate these previous month end dates correctly.
See example below ....
View 9 Replies
View Related
Apr 6, 2012
I am currently using this formula to count dates between 2 date periods If specific condition exists.
=COUNTIFS(
Data!S1:S100000,"*KP*",
Data!X1:X100000,">=10/1/2010",
Data!X1:X100000,"=10/1/2010",
Data!X1:X100000,"
View 3 Replies
View Related
Aug 19, 2014
get other cells in a spreadsheet to populate with the Saturdays of a month based on the date input in another cell.
Example
A1 = October 2014
The following cells would automatically display:
A2 10/04/14
A3 10/11/14
A4 10/18/14
A5 10/25/14
A6 11/01/14
Then if cell A1 is changed to March 2015 the following cells would automatically display:
A2 03/07/15
A3 03/14/15
A4 03/21/15
A5 03/28/15
A6 04/04/15
I can't seem to find a way to get just the Saturdays. +7 doesn't work because Saturday is not always the 7th day of the month.
View 2 Replies
View Related
Jul 20, 2012
This might be a simple date transformation formula that I need
Column A has numbers like: 200517, 200530, 200544, 201036, 201043, etc
I'd like to get a formula in Col B in corresponding rows that would show date as dd/mm/yy
I know the numbers above are yyyyww = yyyy is year, and ww is the week of that year.
So the formula would have to convert that particular week to the day and month. I understand that are 7 days in a week, but if the formula would reflect Monday of that week, it would suffice.
View 2 Replies
View Related
Sep 10, 2013
I have a cell (A1) which contains the month. A2 contains the year. I need a formula which will return each date of the month.
View 7 Replies
View Related