3

Good Afternoon, I'm trying to build a calculated column in SharePoint 2010 that returns 1 of 3 choices based on a date.

The date field is called "Requested Date" and I need to have the column say "Less Than 6" "Greater than 6 Less than 12" and "Greater Than 12" if that is even possible.

I need these options for the requested date to be:

  • Less than 6 months old
  • More than 6 months old but less than 12 months old
  • More than 12 months old
Improve this question
2

3 Answers 3

Reset to default
0

Create calculated column with following formula :

=IF([Requested Date]>TODAY(),"Requested Date greater than today",IF([Requested Date]>TODAY()-180,"Less than 6 months old",IF([Requested Date]>TODAY()-365,"More than 6 months but less than 12 months","More than 12 months")))

Note : we can only compare with days not months so used 180 (for 6 months) and 365 (for 12 months).

in your question one condition is missing : If "requested date" is greater than today, so i have added then condition.

Here is the end result :

enter image description here

Improve this answer
4
  • Wait a couple of hours till the clock says its the next day.. And you will notice this Formula is now one day off so it does not work. --> sharepoint.stackexchange.com/questions/151144/…
    – Danny '365CSI' Engelman
    Nov 8, 2016 at 9:10
  • If the calculation needs to be static based on the date it was entered then above solution will wok. If it needs to be dynamic, then like you said it won't work. It depends on the requirement.
    – Monica Jagani
    Nov 8, 2016 at 9:40
  • Yes.. but.. when you re edit the formula ALL the ListItems will update and thus ALL those today()s will update.. If you want date entered use Created or Modified for changed
    – Danny '365CSI' Engelman
    Nov 8, 2016 at 11:11
  • I have tried the code above and it works for what I need. The calculation can be static. Im not looking for the times to change, just something as a quick reference as to how old the record is. these records get updated all of the time so the formula will refresh. Thank you very much.
    – TroyMike518
    Nov 8, 2016 at 15:38
0

TODAY in a Calculated Column does NOT work!

Do not believe any blogs out there! it does not work!

There are workarounds:

How to use Today and Me in Calculated column

Improve this answer
0

I Have about 99% solved my own question. there were a few steps and then a formula I had to create that isn't quite finished but works.

  1. Created a dummy column names "Today"
  2. Created a calculated column named "Age in Days" with the calculation of =[Today]-[Request Date]
  3. Deleted the column titled "Today"

This allowed me to get the age of a record in days and then I continued to step 4:

  1. Created a calculated column titled "Age in Months" with the formula:

=IF([Age in Days]>365,"More Than 12 Months",IF([Age in Days]<182,"Less Than 6 Months",IF([Age in Days]>183,[Age in Days]<364,"Within 6 Months")))

This has worked perfectly in the sense that I can now filter my records by "More than 6 Months," "More than 12 Months," and "Yes." There in lies the problem, I need the third option to say "Within 6 Months" but only get the value of YES returned. like I said it works for now. Any ideas??

Improve this answer

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.