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).
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/Forums/en-US/category/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.
