Need Additional Condition in equation (2003)

Topic

New Reply

Greetings,

I have the following formula, and need to have 1 more condition. Have tried a couple of ways, but still cannot get it to work.
The formula as listed works as it is intended.

Need to add a condition where IF DH2 is blank, then today – Y2

I just could not figure the formula. This I believe can be the first check in the formula, because if DH2 is blank, the rest would not not be an issue.

AX = Lifecycle state (text field)
DF = Disposition (text field)
DH = Disposition Date
HY = Date Submitted

Y – Creation Date

=IF(OR(AX2=”Incorporated”,DF2=”Change Cancelled”,DF2=”Change Rejected”),DH2-HY2+1,IF(OR(AX2=”Authoring”,AX2=”Executing”,AX2=”Ready”),TODAY()-DH2+1,IF(DH2=””,IF(HY2=””,””,TODAY()-HY2),DH2-HY2+1)))

Thanks,
Brad

Reply | Quote

Replies

You already have a condition with DH2=”” in the formula:

IF(DH2=””,IF(HY2=””,””,TODAY()-HY2)

That seems to contradict your wish to have today – Y2.

Reply | Quote

Ok… let me digest this….

Some how I need to ensure I get a calculated number of days if the change has not been submitted. Field Y is the Create date.

Edited: Maybe I check for NO DISPOSITION in the DF field, then do the calc?

any thoughts?

Brad

Reply | Quote

I don’t know what the purpose of your formula is, and you haven’t actually told us what problem you have expanding it, so…

Reply | Quote

IF(OR(AX2=”Incorporated”,DF2=”Change Cancelled”,DF2=”Change Rejected”),DH2-HY2+1,IF(OR(AX2=”Authoring”,AX2=”Executing”,AX2=”Ready”),TODAY()-DH2+1,IF(DH2=””,IF(HY2=””,””,TODAY()-HY2),DH2-HY2+1)))

Basically, I am Calculating the number of DAYs a item is/has been open.
First it looks a the Lifecycle state (Cell AX2) If it contains Incorporated, or if DF2 contains Change
Cancelled or Change rejected) then calc the diff between Dispositon Date (DH2 and Date Submitted HY2) +1
then IF AX2 is Authoring, Executing or Ready) then calc today – Disposition Date (DH2). If for some
reason disposition date (DH2) is blank, and if date Submitted (HY2) is blank then calc today – date submitted(HY2) IF Disposition date is not blank then calc disposition date – Submitted date (HY2).

I think I would like it to calc the number of days open IF it has not been submitted. If not submitted
then the date submitted would be blank, and there will not be a disposition date.

I think that would get all bases covered.

Creation Date cell is (y2)

First, am I reading the entire fourmla correct from a syntax point? Maybe the calc
IF(DH2=””,IF(HY2=””,””,TODAY()-HY2) is incorrect. maybe it should say Today()-Y2)…

will try and let you know…

EDITED: I changed the formula as such: Removed the AX2=”Authoring” from the formula, and changed
the last section IF(HY2=””,””,TODAY()-HY2), to IF(HY2=””,””,TODAY()-Y2),
Now I get the blank field. However I need to have a calculated days if everything falls through the preceding calcs.

Brad

Reply | Quote

Ok,,, Let’s disregard the previous stuff in this thread.

Attached is a spreadsheet with examples of the various conditions. I put what I thought the formula is doing logic wise (please correct as necessary for future understanding).

I also put the 2 conditions I need to cover within the formula.

Lets go from here….

Thanks,
Brad

Reply | Quote

Does this do what you want?

=IF(AND(DH2=””,AX2=”Authoring”,DF2=”No Disposition”),IF(HY2=””,TODAY()-Y2,TODAY()-HY2),IF(OR(AX2=”Incorporated”,DF2=”Change Cancelled”,DF2=”Change Rejected”),DH2-HY2+1,IF(OR(AX2=”Authoring”,AX2=”Executing”,AX2=”Ready”),TODAY()-DH2+1,IF(DH2=””,IF(HY2=””,””,TODAY()-HY2),DH2-HY2+1))))

Reply | Quote

Hans,

Absolutly,!!!

Can you breakdown this formula Logically? I really am not clear as to what is “does” from point to point.

Thanks again,

Brad

Reply | Quote

=IF(AND(DH2=””,AX2=”Authoring”,DF2=”No Disposition”),IF(HY2=””,TODAY()-Y2,TODAY()-HY2),IF(OR(AX2=”Incorporated”,DF2=”Change Cancelled”,DF2=”Change Rejected”),DH2-HY2+1,IF(OR(AX2=”Authoring”,AX2=”Executing”,AX2=”Ready”),TODAY()-DH2+1,IF(DH2=””,IF(HY2=””,””,TODAY()-HY2),DH2-HY2+1))))

It first checks whether DH is blank, AX contains “Authoring”, and DF contains “No Disposition”.
If so, it looks at HY. If this is blank, compute Today – Y, else compute Today – HY.
Otherwise, use your original formula.

Reply | Quote