Feb 25 2022 06:24 AM
Hello.
I am trying to do some complex calculations in SP and don't even know if SP has the capability.
In a list, I have a Column [Proposed Presentation Date]. There is also a [Created] date column.
I want, in a different column [Days Before Late] to calculate the number of days, then give a message when threshold is met.
Proposed Presentation Date is July 1, 2022. Created Date is February 1, 2022. That would be 150 days exactly but I keep getting a value of 2 when using the following formula:
=TEXT([Proposed Presentation Date]-[Created],"D")
=TEXT([Proposed Presentation Date]-[Created],"D")
The complex part is when this date is properly calculated, if it is over 150 days, then it should be flagged or prompt for a message stating it is late.
Any assistance would be appreciated.
Feb 25 2022 06:50 AM
@mikehamm35 In your calculated column you just need =[Presentation]-[Created] with the result being a number with 0 decimal places. Then you can format the result with JSON column formatting.
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
Feb 25 2022 06:56 AM
@mikehamm35 I forgot to add the screnshot. The calculated column is called Alpha and the formula was =[Expiry]-[Created] and it shows the days between the 2 correctly.
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
Feb 25 2022 09:16 AM
Feb 25 2022 09:40 AM
Aug 23 2022 06:11 AM - edited Aug 23 2022 06:13 AM
@mikehamm35, you have probably figured this out already, but:
{"$schema":"https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json","elmType":"div","attributes":{"class":"= if(Number([$File_x0020_Expires_x0020_On0]) == 0, '' , if([$File_x0020_Expires_x0020_On0] <= @now+86400*15 && [$File_x0020_Expires_x0020_On0] > @now , 'sp-field-severity--warning', if([$File_x0020_Expires_x0020_On0] <= @now, 'sp-field-severity--blocked', if([$File_x0020_Expires_x0020_On0] > @now+86400*75, 'sp-field-severity--good', 'sp-field-severity--good') ) ))"},"children":[{"elmType":"span","style":{"display":"inline-block","padding":"0 4px"},"attributes":{}},{"elmType":"span","txtContent":"@currentField"}]}
This code would go in the Column Formatting field of your calculated column. This is done in SharePoint.
For sending alerts to people who own the past due content, use a Power Automate flow which looks something like this.
You would change the Get File (properties only) step to Get Items.