Calculated Field Formula

[11952]

I have created a calculated field and need some help.

calculated Field name GateDate -datatype is set to Date & Time, I am trying to achieve is if BD
Status = "Qualified Gate 1" and Gate2 Rvw has a date then Gate2 Rvw, "" I am getting a date of 12/26/1899 in some fields and I think I need to somehow in my formula say if Gate1 Rvw ="","" (the Gate1 Rvw, Gate2 Rvw, Gate3 Rvw,
Gate4 Rvw are date fields) FORMULA BELOW

=IF([BD Status]="","",IF([BD Status]="Qualified Gate 1",[Gate1 Rvw],IF([BD
Status]="Qualified Gate 2",[Gate2 Rvw],IF([BD Status]="Qualified Gate 3",[Gate3
Rvw],IF([BD Status]="Qualified Gate 4",[Gate4 Rvw],"")))))

Thank you in advance for your help...

 

[dinger2121]

Did you first try breaking your if statements out individually to ensure they return the correct information from the start? I would first do =IF([BD Status]="[Qualified Gate 1]",[Gat1 Rvw]) just to ensure that the expected dates are displaying for Qualified Gate 1. Once you are certain these individual pieces are working, then begin creating your complete formula.

carl
MCSD, MCTS:MOSS

 

[11952]

Thank you - I have been able to make the one instance work >
=IF([BD Status]="Qualified Gate 1"&[Gate1 Rvw]<"",[Gate1 Rvw],"")

I can't seem to add the other 3 successfully without error, below is just adding the next part of the formula...

=IF([BD Status]="Qualified Gate 1"&[Gate1 Rvw]<"",[Gate1 Rvw],"",([BD Status]="Qualified Gate 2"&[Gate2 Rvw]<"",[Gate2 Rvw],""))

 

[dinger2121]

try something like this

=IF(AND([BD Status]="Qualified Gate 1",[Gate1 Rvw]<""),[Gate1 Rvw],IF(AND(([BD Status]="Qualified Gate 2",[Gate2 Rvw]<""),[Gate2 Rvw],"")))

you might have to play with the closing ), but that may be correct.



carl
MCSD, MCTS:MOSS

 

[11952]

I finally got it:

=IF([Gate1 Rvw]="","",IF([BD Status]="Qualified Gate 1",[Gate1 Rvw],IF([Gate2 Rvw]="","",IF([BD Status]="Qualified Gate 2",[Gate2 Rvw],IF([Gate3 Rvw]="","",IF([BD Status]="Qualified Gate 3",[Gate3 Rvw],IF([Gate4 Rvw]="","",IF([BD Status]="Qualified Gate 4",[Gate4 Rvw],""))))))))

 

[dinger2121]

Great...glad you were able to figure it out.

carl
MCSD, MCTS:MOSS