SOLVED

JSON Sharepoint Date Validation

%3CLINGO-SUB%20id%3D%22lingo-sub-2035067%22%20slang%3D%22en-US%22%3EJSON%20Sharepoint%20Date%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2035067%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I'm%20new%20on%20JSON%20Script.%3CBR%20%2F%3EI've%20problem%20for%20JSON%20Validate%20Date%20on%20Sharepoint%20online.%3C%2FP%3E%3CP%3EI%20just%20write%20on%20sharepoint%20365%20online%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7B%20%22%24schema%22%3A%20%22%3CA%20href%3D%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%3C%2FA%3E%22%2C%20%22elmType%22%3A%20%22div%22%2C%20%22txtContent%22%3A%20%22%3Dif(%40now%26gt%3B%3D%5B%24StartDate%5D%2Cif(%40now%26lt%3B%3D%5B%24EndDate%5D%2C'active'%2C'non%20active')%2C'non%20active')%22%20%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20the%20result%20always%20non%20active%20when%20it%20saved.%3C%2FP%3E%3CP%3EI%20need%20status%20will%20be%20%22active%22%20if%20date%20range%20on%20current%20month%20and%20will%20be%20change%20automatically%20to%20%22Non%20Active%22%20if%20out%20of%20current%20month%3C%2FP%3E%3CP%3EI%20hope%20my%20pic%20can%20tell%20about%20my%20problem%3C%2FP%3E%3CP%3EThanks%20everyone%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2035067%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2035099%22%20slang%3D%22en-US%22%3ERe%3A%20JSON%20Sharepoint%20Date%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2035099%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F919315%22%20target%3D%22_blank%22%3E%40lucasignatius%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20add%20more%20details%20about%20your%20requirement%20%26amp%3B%20conditions%20to%20check%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20want%20to%20show%20the%20column%20value%20as%20%22Active%22%20when%20current%20(today's)%20date%20is%20in%20between%20Start%20date%20and%20End%20Date%3F%20Or%20if%20both%20the%20Start%20date%20and%20End%20date%20are%20in%20current%20month%3F%3C%2FP%3E%3CHR%20%2F%3E%3CP%3EPlease%20click%20%3CSTRONG%3EMark%20as%20Best%20Response%3C%2FSTRONG%3E%20if%20my%20post%20helped%20you%20to%20solve%20your%20issue.%20This%20will%20help%20others%20to%20find%20the%20correct%20solution%20easily.%20It%20also%20closes%20the%20item.%20If%20the%20post%20was%20useful%20in%20other%20ways%2C%20please%20consider%20giving%20it%20%3CSTRONG%3ELike%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2035103%22%20slang%3D%22en-US%22%3ERe%3A%20JSON%20Sharepoint%20Date%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2035103%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373269%22%20target%3D%22_blank%22%3E%40ganeshsanap%3C%2FA%3E%26nbsp%3Bsorry%20for%20my%20question%20is%20have%20a%20little%20missing.%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20mean%20is%20%22Active%22%20when%20current%20(today's)%20date%20is%20in%20between%20Start%20date%20and%20End%20Date.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%20for%20your%20help%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2035194%22%20slang%3D%22en-US%22%3ERe%3A%20JSON%20Sharepoint%20Date%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2035194%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F919315%22%20target%3D%22_blank%22%3E%40lucasignatius%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20using%20below%20JSON%20code.%20It%20should%20work%20for%20you%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3E%7B%0A%20%20%20%20%22%24schema%22%3A%20%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%22%2C%0A%20%20%20%20%22elmType%22%3A%20%22div%22%2C%0A%20%20%20%20%22txtContent%22%3A%20%22%3Dif(%40now%20%26gt%3B%3D%20%5B%24StartDate%5D%20%26amp%3B%26amp%3B%20%40now%20%26lt%3B%3D%20%5B%24EndDate%5D%2C%20'Active'%2C%20'Not%20Active')%22%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EMicrosoft%20documentation%3C%2FSTRONG%3E%3A%26nbsp%3B%3CA%20title%3D%22Use%20column%20formatting%20to%20customize%20SharePoint%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsharepoint%2Fdev%2Fdeclarative-customization%2Fcolumn-formatting%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EUse%20column%20formatting%20to%20customize%20SharePoint%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3CP%3EPlease%20click%20%3CSTRONG%3EMark%20as%20Best%20Response%3C%2FSTRONG%3E%20if%20my%20post%20helped%20you%20to%20solve%20your%20issue.%20This%20will%20help%20others%20to%20find%20the%20correct%20solution%20easily.%20It%20also%20closes%20the%20item.%20If%20the%20post%20was%20useful%20in%20other%20ways%2C%20please%20consider%20giving%20it%20%3CSTRONG%3ELike%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi, I'm new on JSON Script.
I've problem for JSON Validate Date on Sharepoint online.

I just write on sharepoint 365 online

 

{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "=if(@now>=[$StartDate],if(@now<=[$EndDate],'active','non active'),'non active')" }

 

My problem the result always non active when it saved.

I need status will be "active" if date range on current month and will be change automatically to "Non Active" if out of current month

I hope my pic can tell about my problem

Thanks everyone

8 Replies
best response confirmed by lucasignatius (Contributor)
Solution

@lucasignatius 

 

Can you please add more details about your requirement & conditions to check?

 

Do you want to show the column value as "Active" when current (today's) date is in between Start date and End Date? Or if both the Start date and End date are in current month?


Please click Mark as Best Response if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

 

@ganeshsanap sorry for my question is have a little missing.

I mean is "Active" when current (today's) date is in between Start date and End Date.

Thanks for your help

 

 

@lucasignatius 

Try using below JSON code. It should work for you:

 

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "=if(@now >= [$StartDate] && @now <= [$EndDate], 'Active', 'Not Active')"
}

 

Microsoft documentationUse column formatting to customize SharePoint 


Please click Mark as Best Response if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

 

@ganeshsanap 

Hi, I just tried but it still not working status still "not active"

 

@lucasignatius 

 

What is the internal name of Start date and End date columns?

 

In JSON formatting, you need to use internal name of your columns to refer their value in JSON code. Check below documentation to find out the internal names of start date and end date. and use those in place of [$StartDate] and [$EndDate].

 

Find the internal name of SharePoint column 

 

Here is the output of the same code in my SharePoint list:

Json formatting output.PNG

Microsoft documentation[$FieldName] 


 

@ganeshsanap your are the best :cool:

Thanks for your help, anyway do you know how to give color (example, active will be green, non active will be red).

JSON DONE.PNG

@lucasignatius 

 

Use below code for changing colors:

 

 

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "=if(@now >= [$StartDate] && @now <= [$EndDate], 'Active', 'Non Active')",
    "style": {
        "color": "=if(@now >= [$StartDate] && @now <= [$EndDate], 'green', 'red')",
        "font-weight": "bold"
    }
}

 

 

For more information related to styling refer: Use column formatting to customize SharePoint 


If my posts was useful to you, please consider giving it Likes.

 

@ganeshsanap yeah.. it work :cool:

Thanks yoo..

And I'll closed this case