Forum Discussion
Date Calculations in SharePoint List with conditions
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.
5 Replies
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)- Thank you very much. I got the values I need. Now I am trying to create conditional formats which cause an alert to appear if the "Alpha Column" extends a certain amount. I am using InfoPath to create those rules because I don't think SP has that functionality.
mikehamm35, you have probably figured this out already, but:
- If you want an Alert to occur based on days past, the best way to do that is through Power Automate.
- If you want to change the format of your Alpha column, like red if overdue, yellow if 15 days before overdue, etc., then you can use special JSON code to change colors. In the example below, I have formatted for overdue, soon to be due, past due. The JSON code I used, which I found and modified on one of these extremely helpful forums, is as follows:
{"$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.
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)
