Formula Error Message

  • Thread starter Thread starter Frick
  • Start date Start date
F

Frick

I can't seem to get this formula right. I hope someone can help me out here.

IF(B15=0,1,IF(DATE(YEAR($B15),MONTH($B15),1)>E$4,DAYS360(DATE(YEAR($B15),MONTH($B15),1),E$4)/30,IF(DATE(YEAR($B15),MONTH($B15),1)=E$4,"Construction
Start",IF(DATE(YEAR($C15),MONTH($C15)+1,0)>E$5,"Construction",IF(DATE(YEAR($C15),MONTH($C15)+1,0)=E$5,"Construction
Line 1End",IF($C15="",0,ROUND(DAYS360(DATE(YEAR($C15),MONTH($C15)+1,0),E$5)/30,0)),0)))))



b15=start date (if there is one)
c15=finish date (if there is one)

e4 ===> ad4 = 1st day of the month
e5 ===> ad5 = Last day of the month



What this formula should do:

1. Look to see if b15 has a date entered.
2. If it doesn't return 0
3. If it does then it should do one of several things:
3a. Return a negative number in each column for all months prior to start date
3b. Return "Construction Start" in the column that corresponds to the start date
3c. Return "Construction for all months between start date and finish date
3d. Return a postive number +1 for each column month after the finish date

I think where I am having my problem is with the end of the formula where it uses the ROUND
function.

Since this formula drives a number of other formulas throughout the spread sheet it is important
that it return the correct information and not a #VALUE! error message.



Thanks, Frick
 
I can't seem to get this formula right. I hope someone can help me out here.
Click to expand...

[reformatted with underscores for indentation]
IF(B15=0,
__1,
__IF(DATE(YEAR($B15),MONTH($B15),1)>E$4,
____DAYS360(DATE(YEAR($B15),MONTH($B15),1),E$4)/30,
____IF(DATE(YEAR($B15),MONTH($B15),1)=E$4,
______"Construction Start",
______IF(DATE(YEAR($C15),MONTH($C15)+1,0)>E$5,
________"Construction",
________IF(DATE(YEAR($C15),MONTH($C15)+1,0)=E$5,
__________"Construction Line 1End",
__________IF($C15="",
____________0,
____________ROUND(DAYS360(DATE(YEAR($C15),MONTH($C15)+1,0),E$5)/30,0)
__________),
__________0
Click to expand...

The preceding two lines generate a syntax error.
________)
______)
____)
__)
)
Click to expand...
...

As indicated above, there's a syntax error in the formula. The IF function takes
only *3* arguments, but you're trying to pass *4* arguments to the second to
last IF call.

Also, you should check whether C15 is blank or zero *before* trying to use it as
a date. Your YEAR($C15) and MONTH($C15) calls will return errors if C15 is blank
or zero (unless you're using the 1904 date system, but even then it's unlikely
you'd be doing anything with dates in 1904, so instead of useful error values
you'd get garbage numbers).
What this formula should do:
1. Look to see if b15 has a date entered.
2. If it doesn't return 0
3. If it does then it should do one of several things:
3a. Return a negative number in each column for all months prior to start date
3b. Return "Construction Start" in the column that corresponds to the start date
3c. Return "Construction for all months between start date and finish date
3d. Return a postive number +1 for each column month after the finish date

I think where I am having my problem is with the end of the formula where it
uses the ROUND function.
Click to expand...

Correct, but it should have prevented you from entering the formula.

You don't need to use DAYS360(..)/30 to calculate months. Use DATEDIF.

=IF($B15<=0,1,IF($B15-DAY($B15)+1>E$4,-DATEDIF(E$4,$B15-DAY($B15)+1,"M"),
IF($B15-DAY($B15)+1=E$4,"Construction Start",
IF($C15<=0,0,CHOOSE(2+SIGN(DATE(YEAR($C15),MONTH($C15)+1,0)-E$5),
"Construction","Construction Line 1End",
DATEDIF(DATE(YEAR($C15),MONTH($C15)+1,0),E$5,"M"))))))
 
=IF($B15=0,"",IF(($B15>K5),-DATEDIF(K$4,$B15,"M"),IF(AND(($B15<K$5),($B15>K$
4)),"Start",IF(AND(($C15>K$5),($B15<K$4)),"Const",IF(AND(($C15<=K$5),($C15>=
K$4)),"End",DATEDIF($C15,K$5,"M"))))))

You may need to play around with some of the inequalities as they may need
to be <= or >= instead of < or >.
 
Hi Harlan,

Using your formula below, but changing the if b15<= 0, 1 to b15<=0,0, it returns the correct result
except in two areas.

1. Between the Start Dates and Finish Dates, in those cols it should return "Construction" . It is
returning #NUM!

2. In the cols after the Finish Date it should return a number starting with 1 (1st month after
Finish Date) and continue on each month adding 1 to the previous month.

Can this be resolved?

Scott



I can't seem to get this formula right. I hope someone can help me out here.
Click to expand...

[reformatted with underscores for indentation]
IF(B15=0,
__1,
__IF(DATE(YEAR($B15),MONTH($B15),1)>E$4,
____DAYS360(DATE(YEAR($B15),MONTH($B15),1),E$4)/30,
____IF(DATE(YEAR($B15),MONTH($B15),1)=E$4,
______"Construction Start",
______IF(DATE(YEAR($C15),MONTH($C15)+1,0)>E$5,
________"Construction",
________IF(DATE(YEAR($C15),MONTH($C15)+1,0)=E$5,
__________"Construction Line 1End",
__________IF($C15="",
____________0,
____________ROUND(DAYS360(DATE(YEAR($C15),MONTH($C15)+1,0),E$5)/30,0)
__________),
__________0
Click to expand...

The preceding two lines generate a syntax error.
________)
______)
____)
__)
)
Click to expand...
..

As indicated above, there's a syntax error in the formula. The IF function takes
only *3* arguments, but you're trying to pass *4* arguments to the second to
last IF call.

Also, you should check whether C15 is blank or zero *before* trying to use it as
a date. Your YEAR($C15) and MONTH($C15) calls will return errors if C15 is blank
or zero (unless you're using the 1904 date system, but even then it's unlikely
you'd be doing anything with dates in 1904, so instead of useful error values
you'd get garbage numbers).
What this formula should do:
1. Look to see if b15 has a date entered.
2. If it doesn't return 0
3. If it does then it should do one of several things:
3a. Return a negative number in each column for all months prior to start date
3b. Return "Construction Start" in the column that corresponds to the start date
3c. Return "Construction for all months between start date and finish date
3d. Return a postive number +1 for each column month after the finish date

I think where I am having my problem is with the end of the formula where it
uses the ROUND function.
Click to expand...

Correct, but it should have prevented you from entering the formula.

You don't need to use DAYS360(..)/30 to calculate months. Use DATEDIF.

=IF($B15<=0,1,IF($B15-DAY($B15)+1>E$4,-DATEDIF(E$4,$B15-DAY($B15)+1,"M"),
IF($B15-DAY($B15)+1=E$4,"Construction Start",
IF($C15<=0,0,CHOOSE(2+SIGN(DATE(YEAR($C15),MONTH($C15)+1,0)-E$5),
"Construction","Construction Line 1End",
DATEDIF(DATE(YEAR($C15),MONTH($C15)+1,0),E$5,"M"))))))
Click to expand...
 
Using your formula below, but changing the if b15<= 0, 1 to b15<=0,0, it
returns the correct result except in two areas.

1. Between the Start Dates and Finish Dates, in those cols it should
return "Construction" . It is returning #NUM!

2. In the cols after the Finish Date it should return a number starting with
1 (1st month after Finish Date) and continue on each month adding 1 to the
previous month.

Can this be resolved?
Click to expand...

Yes, if I correct the order of the results for the CHOOSE function.

=IF($B15<=0,0,IF($B15-DAY($B15)+1>E$4,-DATEDIF(E$4,$B15-DAY($B15)+1,"M"),
IF($B15-DAY($B15)+1=E$4,"Construction Start",IF($C15<=0,"?",
CHOOSE(2+SIGN(DATE(YEAR($C15),MONTH($C15)+1,0)-E$5),
DATEDIF(DATE(YEAR($C15),MONTH($C15)+1,0),E$5,"M"),"Construction Line 1End",
"Construction")))))

Note that I've changed the entry for months after B15 when there's no date in
C15 from 0 to "?". Shouldn't this be "Construction"?
 
Harlan.

Yes, I see what you did here and it worked. It now provides the count down as a negative number to
Construction start, then returns Construction during the construction phase, returns Construction
End on the construction end month.

However, there is still a problem not resolved. In the formula that I originally used, I had the
Round function. This was to solve the dilema I had with the counting months following the
Construction End. In your formula I get a ) for the first month following the Construction End that
should return a 1. Then the next 2 months returns 2 and 2 follwed by 4 then 5, 5, 7, 7, 9 and so
on. This result is found when the Construction End Date is the last day of the month, which I use
so that the first day of production is the first day of the month.

What the col's should look like is basically this:

.... -3 -2 -1 Con..Start, Cons..., Cons.. , Cons,, Con End.., 1, 2, 3, 4

Hopefully you get the idea.

I think one more pass may do the trick.

Let me also just add how grateful I am for all your help.

Frick
 
...
...
However, there is still a problem not resolved. In the formula that I
originally used, I had the Round function. This was to solve the dilema
I had with the counting months following the Construction End. In your
formula I get a ) for the first month following the Construction End that
should return a 1. Then the next 2 months returns 2 and 2 follwed by 4
then 5, 5, 7, 7, 9 and so on. This result is found when the Construction
End Date is the last day of the month, which I use so that the first day
of production is the first day of the month.

What the col's should look like is basically this:

... -3 -2 -1 Con..Start, Cons..., Cons.. , Cons,, Con End.., 1, 2, 3, 4
Click to expand...
...

Drop the DATEDIF, use direct calculation instead.

=IF($B15<=0,0,IF($B15-DAY($B15)+1>E$4,
(YEAR(E$4)-YEAR($B15))*12+MONTH(E$4)-MONTH($B15),
IF($B15-DAY($B15)+1=E$4,"Construction Start",IF($C15<=0,"?",
CHOOSE(2+SIGN(DATE(YEAR($C15),MONTH($C15)+1,0)-E$5),
(YEAR(E$5)-YEAR($C15))*12+MONTH(E$5)-MONTH($C15),
"Construction Line 1End","Construction")))))

Then again, since your first and last day of month range is E4:AD5, try this
instead for cell E15.

=IF($B15<=0,0,IF(AND(0<$C15,$C15<$B15),"Invalid",IF(E$5<$B15,
COLUMN()-4-MATCH($B15,$E$4:$AD$4),IF(E$4<=$B15,"Construction Start",
IF($C15<=0,"?",IF(E$5<$C15,"Construction",IF(E$4<=$C15,
"Construction Line 1End",COLUMN()-5-MATCH($C15,$E$5:$AD$5))))))))

which adds some error checking to trap end date before start date. This fails to
handle the theoretical possibility in which construction begins and ends in the
same month. To handle that as well, try

=IF($B15<=0,0,IF(AND(0<$C15,$C15<$B15),"Invalid",IF(E$5<$B15,
COLUMN()-4-MATCH($B15,$E$4:$AD$4),IF(E$4<=$B15,IF($C15<=E$5,
"Construction Start and Line 1End","Construction Start"),IF($C15<=0,"?",
IF(E$5<$C15,"Construction",IF(E$4<=$C15,"Construction Line 1End",
COLUMN()-5-MATCH($C15,$E$5:$AD$5))))))))
 
Harlan,

Well, this is getting interesting. So far I have learned about the CHOOSE, MATCH and several other
commands. Thank you.

Still the problem... with your formulas listed below, everything is still fine until we reach the
Construction End Date and the counting numbers begin. Both your formulas return a value creater
than 1 after the construction end date. Howerver, at leeast they are not duplicates as before.

One more try?

Frick
 
...
...
Still the problem... with your formulas listed below, everything is still
fine until we reach the Construction End Date and the counting numbers begin.
Both your formulas return a value creater than 1 after the construction end
date. Howerver, at leeast they are not duplicates as before.

One more try?
Click to expand...

Not yet. That last set of formulas I tested with month beginning and ending
dates in E4:AD5 and some but not many different beginning and ending dates in
B15 and C15. All the formulas worked for me using that data. Note: my E4:AD5 are
filled in as E4 = an arbitrary base date which I set to 1-Jul-2002; E5:
=EOMONTH(E4,0); F4: =E5+1. Then I filled E5 into F5 then filled F4:F5 into
G4:AD5, so the pairs in rows 4 and 5 from column E through AD represent
sequential one month periods with no gaps. If you're using something else, stick
with the first formula I provided in my last response.

This time you get to provide the dates causing you problems. I'm not going to
try guessing. You need to show what *ALL* the dates in E4:AD5 are as well as
pairs of column B and C beginning and ending dates that are having problems.
 
Ok here are my formulas

In E4 I have entered a date - 7/1/03
In E5 I have a formula (DATE(YEAR(E4),MONTH(E4)+1,DAY(E4)-1))

In cols after Col E row 4 I have formula: (DATE(YEAR(E4),MONTH(E4)+1,DAY(E4)))
In cols following row 5 I have same formula as above in E5 copied across cols

No gaps across col's, each month adds 1 more from previous.

B is a entered date like 8/1/04
C is a formula date; DATE(YEAR(a18),MONTH(a18)+a19,DAY(a18)-1)) where a19= months of construction
like 17.

That's it.

Maybe I should replace my row 5 formula with your EOMONTH formula?

Frick
 
...
...
No gaps across col's, each month adds 1 more from previous.
Click to expand...
...

I hadn't tested it sufficiently. The first formula in my response before last
DID WORK. The other two didn't work when construction ended on the last day of a
month. The second and third formulas should be

=IF($B15<=0,0,IF(AND(0<$C15,$C15<$B15),"Invalid",IF(E$5<$B15,
COLUMN()-4-MATCH($B15,$E$4:$AD$4),IF(E$4<=$B15,"Construction Start",
IF($C15<=0,"?",IF(E$5<$C15,"Construction",IF(E$4<=$C15,
"Construction Line 1End",COLUMN()-4-MATCH($C15,$E$4:$AD$4))))))))

and

=IF($B15<=0,0,IF(AND(0<$C15,$C15<$B15),"Invalid",IF(E$5<$B15,
COLUMN()-4-MATCH($B15,$E$4:$AD$4),IF(E$4<=$B15,IF($C15<=E$5,
"Construction Start and Line 1End","Construction Start"),IF($C15<=0,"?",
IF(E$5<$C15,"Construction",IF(E$4<=$C15,"Construction Line 1End",
COLUMN()-4-MATCH($C15,$E$4:$AD$4))))))))

respectively.
 
I made the changes that you provided in the range E4:ad5. That seems simpler then what I had.

I tested the two formulas below, however both still return the wrong value after construction end
date. As you mentioned, the first formula did work. I hadn't tried it as I believed that your
further iterations would have been better as yo u indicated as a check.

So, I will use the first formula, unless you think it would be better to resolve the others below.

Frick


PS...
I have one other challege that I need some help on concernig dates. I will post it as a new message
and hope you can help. You always have a solution and I appreciate your patience.
 
Frick said:
I tested the two formulas below, however both still return the
wrong value after construction end date. . . .
Click to expand...
....

The formulas work for me. Here's a link to a copy of my test file.

ftp://members.aol.com/hrlngrv/test.xls

Enter beginning and ending dates in B7 and C7, respectively, to make E7:AD7
(for the first formula) and E8:AD8 (for the second formula) evaluate
incorrectly. Then let me know what those beginning and ending dates are.
 
Back
Top