1

I am trying to generate column called status_mode which should have the outputs DONE, PENDING, or DELAYED. But I am comparing two columns one is DATE REQUESTED and other is FAD_CODE. Below is my formulae.

=IF(FAD_CODE>1,"DONE","PENDING"),if(DATE REQUESTED+3,"DELAYED","PENDING")

Conditions i am applying.

  • If FAD_CODE field is blank it should show Pending
  • If FAD_CODE got value it should show DONE
  • If FAD_CODE field is blank for three days it should show DELAYED

please help.

Improve this question
3
  • it is quite tough to create calculated formulas in Sharepoint Try to use excel to get what you want (the engine is pretty much the same) cnblogs.com/batter152/p/4503696.html
    – Marek Sarad
    Commented Feb 13, 2019 at 14:16
  • What is the type of FAD_CODE field? Date or number or something else?
    – Ganesh Sanap - MVP
    Commented Feb 13, 2019 at 15:23
  • Hi Ganesh, the FAD_CODE field type is text
    – Raj
    Commented Feb 14, 2019 at 16:03

3 Answers 3

Reset to default
3

Note: [Today] isn't typically accepted in Calculated Columns (there are workarounds) but you can alternatively use the function Today() in its place! The original command below the divide would therefore be this:

=if(NOT([FAD_CODE]=""),"DONE",if(([DATE_REQUESTED]+3)>=Today(),"DELAYED","PENDING"))

Try something like this:

=if(NOT([FAD_CODE]=""),"DONE",if(([DATE_REQUESTED]+3)>=[Today],"DELAYED","PENDING"))

It first checks if FAD_CODE is not blank. If FAD_CODE is not blank, outputs DONE. If it is blank, it checks to see if the DATE_REQUESTED + 3 is greater than today. If it is, the output is DELAYED, Otherwise it's PENDING.

This may not be perfect, so try it out, do some fiddling, and let me know what happens if it doesn't work properly.

Improve this answer
4
  • Hi Glasier, thanks for reply. The formulae didn't work. Below is the error message. Calculated columns cannot contain volatile functions like Today and Me.
    – Raj
    Commented Feb 14, 2019 at 15:53
  • @Raj Apologies, forgot about that restriction. Try replacing [Today] with Today() and see if that works. I believe that the variable [Today] doesn't work because its a variable that's volatile and doesn't update when its supposed to in lists. Today() is a function, which should(?) update. I'll reflect this in my answer.
    – KGlasier
    Commented Feb 14, 2019 at 16:02
  • Hi Glasier, thank you it worked perfectly, but i did little change. Please see below. =if(NOT([FAD]=""),"DONE",if(([DATE]+3)>=Today(),"PENDING","DELAYED"))
    – Raj
    Commented Feb 14, 2019 at 16:13
  • Sorry @Raj I just used the variables you had in your original question. Please note though, I don't know if the Today() will update every day. If it doesn't you'll need to either set some sort of workflow system to update the item every day or just edit any column to refresh the values. Also, if you could mark my answer as "Approved" (the checkmark to the left) that would be appreciated.
    – KGlasier
    Commented Feb 14, 2019 at 16:19
0

I think this one will resolve your problem :

=IF( [FAD_CODE]="", "PENDING", 
   IF( ([DATE_REQUESTED]+3)>=[Today], "DELAYED", 
     IF([FAD_CODE]>1, "DONE" ) ) )
Improve this answer
2
  • Hi Sayyed, it didn't work. Please is below error message.Calculated columns cannot contain volatile functions like Today and Me
    – Raj
    Commented Feb 14, 2019 at 16:04
  • Actually problem is in Date.. Try to search how to implement Date in Calculated Columns
    – Sayyed Amir Hussain
    Commented Feb 14, 2019 at 17:22
0

After some search i got this one. Try this one it may be helpful for you .. You cannot directly specify today and me in calculated column to set the values. You will have to create columns named Today and Me and then use it in your calculated column.

Once you are done with the formula for the calculated column, you need to delete the 2 columns created for this. I think this will make your things work.

Improve this answer
2
  • You can not use [Today] in calculated columns without your stated method, but you can use Today(). This is a build in function that gets the current date, rather than a dynamic variable. You can see in the comments of my post that Raj was able to get the formula to work with the function =if(NOT([FAD]=""),"DONE",if(([DATE]+3)>=Today(),"PENDING","DELAYED"))
    – KGlasier
    Commented Feb 14, 2019 at 17:36
  • Thanx for information
    – Sayyed Amir Hussain
    Commented Feb 15, 2019 at 5:39

Your Answer

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

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