1

I would like to have a column in which the date is calculated from the values in three other columns. I have one column named "Last Test Date", a second column called "Type", a third column called "Insulation" and the forth column called "Next Test Date.

I need the "Next Last Date" column to be calculated so that it updates automatically when the "Last Test Date" date is changed, and basis the output value on "Type" + "Insulation" + "Last Test Date" values.

e.g.

  • if "Last Test Date" equals 1/1/2021 and "Type" equals Winch, then "Next Date Test" equals 1/4/2021.(3 monthly)
  • if "Last Test Date" equals 1/1/2021 and "Type" equals Cage, then "Next Date Test" equals 1/6/2021. (6 monthly)
  • if "Last Test Date" equals 1/1/2021, "Type" equals Winch, and Insulation<=500 then "Next Date Test" equals 31/1/2021. (monthly)
  • if "Last Test Date" equals 1/1/2021, "Type" equals Cage, and Insulation <=500 then "Next Date Test" equals 1/4/2021. (3 monthly)

I tried with below but the last two conditions it seems not reflected

=IF(Type="Winch",[Last Test Date]+90,IF(Type="Cage",[Last Test Date]+180,IF(AND(Type="Winch",[Insulation (MΩ)]<=500),[Last Test Date]+30,IF(AND(Type="Cage",[Insulation (MΩ)]<=500),[Last Test Date]+90))))

Any assistance is greatly appreciated.

Resolved with below code:

=IF(AND(Type="Winch",[Insulation (%)]<=50),[Last Test Date]+30,IF(AND(Type="Cage",[Insulation (%)]<=50),[Last Test Date]+90,IF(Type="Winch",[Last Test Date]+90,[Last Test Date]+180)))
Improve this question

1 Answer 1

Reset to default
1

Please use the following Calculated column formula:

=IF(AND(Types="Winch",Insulation<=500),DATE(YEAR([Last Test Date]),MONTH([Last Test Date])+1,DAY([Last Test Date])),IF(AND(Types="Winch",Insulation>500),DATE(YEAR([Last Test Date]),MONTH([Last Test Date])+3,DAY([Last Test Date])),IF(AND(Types="Cage",Insulation<=500),DATE(YEAR([Last Test Date]),MONTH([Last Test Date])+3,DAY([Last Test Date])),IF(AND(Types="Cage",Insulation>500),DATE(YEAR([Last Test Date]),MONTH([Last Test Date])+6,DAY([Last Test Date]))))))

enter image description here

Improve this answer
2
  • Thank youver much for this proper syntax :)
    – Steven
    Jan 27, 2021 at 1:51
  • Hi, Steven. Thanks for your reply. Glad I can help you solve issue. Have a nice day!
    – EchoDu_MSFT
    Jan 27, 2021 at 1:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.