1

I have a library with a column 'Expiry Date'. I wish to create an additional column to advise the user if a particular entry needs to be reviewed.

The parameters for this is if the 'Expiry Date' is 14 days or less from Todays' date.

In the filter I understand you can't use [Today] and have tried the workarounds but without much luck.

I'm fairly new to calculated columns so any assistance would be greatly appreciated.

Elliott Cook
  • 31
  • 1
  • 4

2 Answers2

0

Calculated formula should be like:

=IF([ExpiryDate]-14 <= NOW(),"Expired","Not Expired")
Ronak Patel
  • 3,261
  • 3
  • 23
  • 45
  • Many thanks Ronak. I have tried the above formula and although works in principle, still not quite what I was after. i.e. I have an Expiry Date of 28th Nov 2016 which is saying Not Expired which is correct, but I would only wish to show items in the list with an Expiry Date of 14 days or less from current date. Any ideas on how to do this? – Elliott Cook Oct 06 '16 at 07:27
  • If you want to show only those items which are expired then create list view & filter with 'CalculatedColumn1' is equal to 'Expired' – Ronak Patel Oct 06 '16 at 07:36
  • So if I have an Expiry Date of say 31 Dec 2016 showing. How do I go about ensuring in that list only Todays Date (i.e. 06th October) up to 20th October is only refelcting. i.e. NOW()+14. – Elliott Cook Oct 06 '16 at 11:54
  • means ? have you created view with filter on calculated column ? ,.. then only expired items will be shown.. – Ronak Patel Oct 06 '16 at 12:01
  • 1
    Remember: Today and Now in Calculated Formulas do NOT auto update: http://sharepoint.stackexchange.com/questions/151144/how-to-use-today-and-me-in-calculated-column/151336#151336 – Danny '365CSI' Engelman Oct 17 '16 at 09:00
0

My question to you is, are you trying to just filter out anything that expired in the last 2 weeks?, or do you want to actually change the status of those items in a separate column?

If you are only looking to hide them from view, in the filter section of the View Settings...

Show the items when column:

Expiry Date | is less than | [Today]

AND

Expiry Date | is greater than or equal to | [Today]-14

This is a view not a calculated column, so there is no "Status" column identifying the item as 'Expired', they simply will not show.

If you want a column that specifically labels items as 'Expired' you can use a Power Automate Flow:

  1. Add a new column with name Status, type Single Line of text, and default value of 'Active' or 'Not Expired' or whatever you want
  2. Create a Scheduled Flow set to run at 12:15am every 1 day
  3. Add action Get Items, set the Site Name and List Name and type into the ODATA Filter: "Expiry_x0020_Date lt "
  4. Use the expression builder and enter: "formatDateTime(utcNow(), 'yyyy-MM-dd')"; click Add to add the date information as a variable that will update daily; this will grab all items with Expiry Date before today.
  5. Add action Update Item; set the Site Name and List Name; and use the dynamic content button to select the ID from the Get Items action; this will cause the Get Items action to be encapsulated in a For Each container
  6. Expand the Advanced Parameters and for the Status field, enter Expired
  7. Add action Terminate

Save. Your flow is now ready to go and will update the Status field for all items with an Expiry Date before today to Expired at 12:15am every day. You can manually trigger the flow by running a test and that will also change over all of the currently expired ones.

You can then create a list view to include or exclude items that are Expired, and/or use Conditional Formatting to highlight them.

If you want other options on your Status field, you can:

  • Set the type to Choice
  • Create the list of choices, and a default value
  • Add "Status ne [replace with excluded status]" to your ODATA Filter; this will cause the Get Items action to ignore that status

You can use the steps above as a foundational framework for setting up additional updates and ODATA filters to change the Status, or other fields based on date.

Hope this helps, sorry its a few years late.

Helpful Links:

Jay Babcock
  • 21
  • 1
  • 8