On Oct 29, 5:35*pm, Jumma Khan <orange...@yahoo.com> wrote:
> I have the following semi-complex if statement:
>
> IF(OR(7/31/2009>X2,7/31/2010<Y2),
> IF(7/31/2009-X2<=30,((7/31/2009-X2)/31)*G2,
> IF(7/31/2009-Y2<=30,((7/31/2009-Y2)/31*G2),G2,0)
First, you cannot write a date as 7/31/2009 in an expression. That is
interpreted as the numeric expression 7 divided by 31 divided 2009.
Generally, it is better to use the DATE function, e.g.
DATE(2009,7,31). Thus, your date will be interpreted correctly
regardless of the Regional and Language setting, m/d/yyyy or d/m/yyyy
et al.
You can get away with writing "7/31/2009" if you use the string in an
arithmetic expression, e.g. --"7/31/2009">X2 or "7/31/2009"-X2. But
the first form is error-prone; if you inadvertently omit the double
negation (--), you are comparing a string and a number. That does not
generate an error, but it does not do the comparison you expected.
> Basically a project has a start date and end date.
I assume that X2 is the start date, Y2 is the end date, and G2 is
monthly project cost.
> If the project encompasses the entire month of July 2009,
> the project is expensed the entire amount for that whole month.
>*If the project starts or ends w/in July 2009, the number of days
> divided by 31 times the monthly cost is allocated for that project
> for that month. *Otherwise, if the project does not start or end in
> July 2009, there is zero costs of the project.
=G2*MAX(0,1+MIN(Y2,DATE(2009,7,31))-MAX(X2,DATE(2009,7,1)))/31
However, I doubt that you really want a formula that works only for
July 2009. More generally, put the first and last dates of the month
in helper cells, e.g. Z2 and AA2. Then you would write:
=G2*MAX(0,1+MIN(Y2,AA2)-MAX(X2,Z2))/(AA2-Z2+1)
The last date in AA2 might be the formula =EOMONTH(Z2,0).
Alternatively, if you cannot use EOMONTH, then use =DATE(YEAR(Z2),
1+MONTH(Z2),0).
> > On Tuesday, October 09, 2007 11:45 AM Karen5 wrote:
For future note, it is a bad idea to post a new question as a response
to an old question, especially a discussion that is 2 years old. Many
people will not see your new posting. Start a new discussion.
Also, for broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/...ry/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.
Similar Threads
