0

This is regarding on employee Status whether Active, Inactive or Archived.

Status is active when [Date End] column is blank or greater than TODAY. status is Archived when [date end]'s YEAR is less than Today's YEAR(ex. 2012<2017) OR [date end]'s month is less than Today's month(ex. 03<04) AND [date end]'s year is less than Today's year(ex. 2012<2017)

below is my code

=IF(OR(ISBLANK([Date End]),[Date End]>Today),"Active",(OR(VALUE(TEXT([Date End],"yyyy"))<VALUE(TEXT(Today,"yyyy")),AND(VALUE(TEXT([Date End],"m"))<VALUE(TEXT(Today,"m")),(VALUE(TEXT([Date End],"yyyy")))<VALUE(TEXT(Today,"yyyy"))),"Archived","Inactive")))

SharePoint is accepting my code but the value is either Active or #VALUE! I don't understand and cant find the error. please help!

Improve this question
4
  • You can not use Today in a Calculated Column: sharepoint.stackexchange.com/search?tab=votes&q=today – Danny '365CSI' Engelman Apr 21 '17 at 6:25
  • As rightly said by @Danny'365CSI'Engelman. We can only use today if our item is going to get edited on daily basis. Else Today will only work once you add / edit the item. – Monica Jagani Apr 21 '17 at 6:29
  • @MonicaJagani, yep! im updating it everyday but TODAY column is not the issue here guys :) – Jake russel Palma Apr 21 '17 at 7:11
  • what is actual logic of setting Archived status .... according to your requirement it should be less than current month (including year) ... is that correct ? – Monica Jagani Apr 24 '17 at 5:11
1

I reformatted your Formula,

Looks like you are missing an IF

=IF( OR( ISBLANK([Date End]) ,[Date End]>Today ) ,"Active" ,( <==== You missed an IF here? OR( VALUE(TEXT([Date End],"yyyy"))<VALUE(TEXT(Today,"yyyy")) ,AND( VALUE(TEXT([Date End],"m"))<VALUE(TEXT(Today,"m")) ,(VALUE(TEXT([Date End],"yyyy")))<VALUE(TEXT(Today,"yyyy")) ) ,"Archived" ,"Inactive" ) ) )

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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