Having problems getting a forumla to work - how do I return a column's value in an If statement?

Column 1, Choice, "AwardType", choices are: AAM, ARCOM, MSM, LOM
Column 2, Date, "LossDate" (date individual is leaving the organization)
Column 3, Calc, "AAMDateDue: "=LossDate-90" (date award must be turned in, 90 days before the loss date, returns date)
Column 4, Calc, "ARCOMDateDue: "=LossDate-120" (date award must be turned in, returns date)
Column 5, Calc, "AwardDueBN": =IF(AwardType="AAM","[AAMDateDue]",IF(AwardType="ARCOM","[ARCOMDateDue]","DefaultValueIfNeither")

I've tried doing this a few different ways (playing with calculated fields to work the dates, converting them to numbers, etc.) and can't seem to get it to return a column's value - it just returns "[AAMDateDue]".

Thanks in advance!

hi papachop

bellow Code worked for me:

AAMDateDue:=TEXT([LossDate]-90;"dd mmm yy")
ARCOMDateDue:=TEXT([LossDate]-120;"dd mmm yy")
AwardDueBN=IF(AwardType="AAM";AAMDateDue;IF(AwardType="ARCOM";ARCOMDateDue;"DefaultValueIfNeither"))

• Edited by Ilqar Alizade 17 hours 1 minutes ago

I get syntax error from both.

dose you'r AAMDateDue and ARCOMDateDue field

Yeah, they work fine - that formula is simple, just using:

=LossDate-90

Data type returned is Date and Time.

hi papachop

bellow Code worked for me:

AAMDateDue:=TEXT([LossDate]-90;"dd mmm yy")
ARCOMDateDue:=TEXT([LossDate]-120;"dd mmm yy")
AwardDueBN=IF(AwardType="AAM";AAMDateDue;IF(AwardType="ARCOM";ARCOMDateDue;"DefaultValueIfNeither"))

• Edited by Ilqar Alizade Wednesday, August 19, 2015 2:39 PM

return data types must be text because AwardDueBN field datatype is text

I'd leave your columns 1-4 alone, leave col 3/4 returning as date.

It's returning the [AAMDateDue] as text because you've got it in quotes - it's not resolving the field its just giving you the value Just remove the quotes. This will work if column 5 is returned as a date/time. You're also missing the last paren.

=IF(AwardType="AAM",[AAMDateDue],IF(AwardType="ARCOM",[ARCOMDateDue],"DefaultValueIfNeither"))

If column 5 is to be returned as text, I'd do

=IF(AwardType="AAM",Text([AAMDateDue],"MM/dd/yyyy"),IF(AwardType="ARCOM",Text([ARCOMDateDue], "MM/dd/yyyy"),"DefaultValueIfNeither"))

Change so date format is what you need.