If and/or HELP?

[southwoman]

Hello,


I havthe following delimna. I would like to create a formula that would allow me ascertain the costs between two dates


Date 1: 1 April 2011

Date 2: 30 August 2012

Cost: $25000

Build months: 4


Todays date


What I am trying to do is to build a forumal that would allow me to


If Date 1 is less than todays date and Date two is less than todays date then use the TOTAL Cost $25k


If date 1 is before todays date but date is after then use the total cost divided by the number of build months multiplied by the numbers of months passed todate.


If the PC date is after todays then Build cost would be $0


Does this make sense?


Thanks in advance


Nicole

 

[Luke M]

In your example, why is build months only 4? I would think it would either be 5 (April,May, June, July, August) or actually 17, since it's from 2011 to 2012. Can you explain?

 

[NARAYANK991]

Hi Nicole ,


Can you please specify your problem in the following manner :


1. Given two dates A and B.


2. Given the Build Cost C.


Now , output to be calculated as follows :


a. If today's date is less than both A and B


b. If today's date is between A and B


c. If today's date is greater than both A and B.


Once this is specified , the IF statement can be easily formulated.


Narayan

 

[Luke M]

Ignoring the month descrepency, I believe this is the formula you are looking for:

=MAX(0,(YEAR(MIN(TODAY(),A3))-YEAR(A2))*12+MONTH(MIN(TODAY(),A3))-MONTH(A2))/((YEAR(A3)-YEAR(A2))*12+MONTH(A3)-MONTH(A2))*A4


Note that I used the formula from here rather than using the sometimes buggy DATEDIF function. Formula takes the elapsed months divided by total possible elapsed months and multiplies by dollar amount.


Assumptions:

Date1 is in A2

Date2 is in A3

Dollars are in A4


Explanation of formula logic:

Let's use Narayan nomencalture for dates. There are 3 scenarios to account for, C < A, A<C<B, or C>B.


If C < B, then we want the smaller date (C) and we'll subtract A.

If B < C, we'll still want the smaller date (B), and we'll subtract A.

Thus, we can start with:

MIN(B,C)-A


However, if C < A, then when we do the subtraction, we'll get a negative number. We want our smallest value to be 0, so we use the MAX function to give us either the result of subtraction, or 0.

=MAX(0,MIN(B,C)-A)


The extra length of the formula is due to having to convert everything to months and keeping accurate count for dates that span different years.

 

[southwoman]

thank you very much. After i broke the formulaic needs down I was able to create the formula thanks so much