Forum Discussion

GeraldT330's avatar
GeraldT330
Copper Contributor
Feb 17, 2023

Syntax to change date format and expiry date

{
"elmType": "div",
"txtContent": "@currentField",
"style": {
"color": "=if(@currentField <= @now, 'crimson',if(@currentField <= @now + 15778800000 && @currentField > @now,'orange','black'))",
"font-weight": "=if(@currentField <= @now, 'bold',if(@currentField <= @now + 15778800000 && @currentField > @now,'bold','normal'))",
"font-size": "=if(@currentField <= @now, '14px',if(@currentField <= @now + 15778800000 && @currentField > @now,'14px','normal'))"
}
}
 
 
Is there a way to format my list based on dates, the current one i copied is from a forum.
i would like the date to be changed to 3 months instead of 6 months. may i know which part of the syntax should i change?  

 

 
Microsoft Lists
SharePoint Online
  • SvenSieverding's avatar
    SvenSieverding
    Feb 17, 2023

    Hi GeraldT330 ,

     

    The formula is checking if the value of the column is smaller than 15778800000 ms in the future and then paints it orange. 


    15778800000 ms seconds are

    15778800000 ms = 1000 (ms) * 60 (seconds) * 60 (minutes) * 24 (hours) * 182.625 (days)

    So if you want just 90 days use

    1000 * 60 * 60 * 24 * 90 = 7776000000


    That means you just have to exchange 15778800000 with 7776000000 in your formula:

    {
	"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
	"elmType": "div",
	"txtContent": "@currentField",
	"style": {
		"color": "=if(@currentField <= @now, 'crimson',if(@currentField <= @now + 7776000000 && @currentField > @now,'orange','black'))",
		"font-weight": "=if(@currentField <= @now, 'bold',if(@currentField <= @now + 7776000000 && @currentField > @now,'bold','normal'))",
		"font-size": "=if(@currentField <= @now, '14px',if(@currentField <= @now + 7776000000 && @currentField > @now,'14px','normal'))"
	}
}


    Best Regards,
    Sven

     

  • SvenSieverding's avatar
    SvenSieverding
    Bronze Contributor
    Feb 17, 2023

    Hi GeraldT330 ,

     

    The formula is checking if the value of the column is smaller than 15778800000 ms in the future and then paints it orange. 


    15778800000 ms seconds are

    15778800000 ms = 1000 (ms) * 60 (seconds) * 60 (minutes) * 24 (hours) * 182.625 (days)

    So if you want just 90 days use

    1000 * 60 * 60 * 24 * 90 = 7776000000


    That means you just have to exchange 15778800000 with 7776000000 in your formula:

    {
	"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
	"elmType": "div",
	"txtContent": "@currentField",
	"style": {
		"color": "=if(@currentField <= @now, 'crimson',if(@currentField <= @now + 7776000000 && @currentField > @now,'orange','black'))",
		"font-weight": "=if(@currentField <= @now, 'bold',if(@currentField <= @now + 7776000000 && @currentField > @now,'bold','normal'))",
		"font-size": "=if(@currentField <= @now, '14px',if(@currentField <= @now + 7776000000 && @currentField > @now,'14px','normal'))"
	}
}


    Best Regards,
    Sven

     

Resources