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.

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 Feb 13 '19 at 14:16
  • What is the type of FAD_CODE field? Date or number or something else? Feb 13 '19 at 15:23
  • Hi Ganesh, the FAD_CODE field type is text
    – Raj
    Feb 14 '19 at 16:03
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.

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
    Feb 14 '19 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
    Feb 14 '19 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
    Feb 14 '19 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
    Feb 14 '19 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" ) ) )
2
  • Hi Sayyed, it didn't work. Please is below error message.Calculated columns cannot contain volatile functions like Today and Me
    – Raj
    Feb 14 '19 at 16:04
  • Actually problem is in Date.. Try to search how to implement Date in Calculated Columns Feb 14 '19 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.

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
    Feb 14 '19 at 17:36
  • Thanx for information Feb 15 '19 at 5:39

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.