May 07 2020 07:35 AM
I've created a custom list in SharePoint online to track our department training. I have a date and time column titled "Expiration" that will list the date that the training/certification will expire. I would like to add a formula to this column that will turn orange once it's 30 days from expiration and then turn red once it's 7 days from expiration. I've searched online and I haven't found a formula that quite matches what I'm looking to do.
I've posted this in the regular SharePoint forum and was advised to post it here.
Thank you.
May 07 2020 08:53 AM
Hello,
Please find these two links:
https://microsoft365.today/microsoft-365/column-formatting-to-customize-sharepoint-list/
https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting
And let me know if you need more help.
Alireza
May 07 2020 10:41 AM
@Alireza Rahimifarid Thank you. I've already read those two articles prior to reaching out to the community. The part I'm having issues with is integrating a formula. As I stated in my post, I would like to add a formula to this column that will turn orange once it's 30 days from expiration and then turn red once it's 7 days from expiration.
May 07 2020 11:45 AM
Here is the code:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"color": "=if([$Expiration] <= @now +7 , '#ff0000', '')"
}
}
May 07 2020 02:11 PM - edited May 07 2020 02:14 PM
@Alireza Rahimifarid I don't think that will give @ChiefKeefe the correct result, partly because it doesn't address the second part of the question about the expiration in the next 30 days, but also because the date comparison needs to be done with milliseconds. So 7 days in the future is @now + 604800000 and 30 days is @now + 2592000000.
So Linda my solution is as follows and my column is called NextReview:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"color": "=if([$NextReview] <= @now + 604800000, '#d9184b', if([$NextReview] > @now+604800000 && [$NextReview] <= @now + 2592000000, '#ff9a00','#585153')"
}
}
which gives the following result:
Rob
Los Gallardos
Microsoft Power Automate Community Super User
May 07 2020 02:26 PM
Solution@ChiefKeefe if you wanted to make it stand out a bit more then you could apply the formatting with a background color:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if([$NextReview] <= @now + 604800000, '#d9184b', if([$NextReview] > @now+604800000 && [$NextReview] <= @now + 2592000000, '#ff9a00','#dddeee')",
"color": "white",
"padding-left": "10px"
}
}
which gives the following result:
Rob
Los Gallardos
Microsoft Power Automate Community Super User
May 07 2020 02:46 PM
@RobElliott and @Alireza Rahimifarid Thanks so much for the assistance! I really appreciate it!
May 07 2020 02:26 PM
Solution@ChiefKeefe if you wanted to make it stand out a bit more then you could apply the formatting with a background color:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if([$NextReview] <= @now + 604800000, '#d9184b', if([$NextReview] > @now+604800000 && [$NextReview] <= @now + 2592000000, '#ff9a00','#dddeee')",
"color": "white",
"padding-left": "10px"
}
}
which gives the following result:
Rob
Los Gallardos
Microsoft Power Automate Community Super User