Modern List View Formatting with UK Dates

%3CLINGO-SUB%20id%3D%22lingo-sub-270383%22%20slang%3D%22en-US%22%3EModern%20List%20View%20Formatting%20with%20UK%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-270383%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20use%20the%20new%20view%20formatting%20in%20the%20Modern%20UI%20lists%20and%20am%20coming%20up%20against%20an%20issue%20with%20the%20date%20format.%26nbsp%3B%20I%20want%20to%20colour%20all%20rows%20red%20where%20the%20Due%20Date%20column%20value%20is%20equal%20to%20or%20past%20today's%20date%20using%20the%20following%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E%3CSPAN%20class%3D%22pun%22%3E%7B%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22str%22%3E%22schema%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3A%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22str%22%3E%22%3CA%20href%3D%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fview-formatting.schema.json%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fview-formatting.schema.json%3C%2FA%3E%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22str%22%3E%22additionalRowClass%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3A%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22str%22%3E%22%3Dif(%5B%24Due_x0020_Date%5D%20%26lt%3B%3D%20%40now%20%26amp%3B%26amp%3B%20%5B%24Status%5D%20%3D%3D%20'1%20-%20Open'%2C%20'sp-field-severity--severeWarning'%2C%20'')%22%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pun%22%3E%7D%3C%2FSPAN%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22pun%22%3E%3CSPAN%3EMy%20sites%20are%20UK-based%20so%20the%20date%20format%20is%20dd%2Fmm%2Fyyyy.%26nbsp%3B%20However%20when%20the%20formatting%20is%20being%20applied%20it%20seems%20to%20be%20looking%20at%20the%20date%20in%20the%20US%20format%20of%20mm%2Fdd%2Fyyyy.%26nbsp%3B%20With%20the%20UK%20format%20set%20for%20my%20site%20the%20item%20with%20a%20due%20date%20of%2011%2F10%2F2018%20doesn't%20appear%20as%20red%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22pun%22%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20276px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F56098i9A72123B61E3B75C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22UK%20Format.PNG%22%20title%3D%22UK%20Format.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22pun%22%3E%3CSPAN%3EHowever%2C%20if%20I%20change%20the%20site%20settings%20to%20use%20the%20US%20date%20format%20it%20recognises%20that%20this%20date%20(11th%20October)%20has%20passed%20and%20colours%20the%20row%20red%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22pun%22%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20276px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F56099iE0178AAB877BC510%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22US%20Format.PNG%22%20title%3D%22US%20Format.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAs%20my%20site%20date%20settings%20need%20to%20stay%20as%20UK%20format%2C%20is%20there%20a%20way%20I%20can%20add%20any%20code%20to%20my%20formatting%20to%20force%20it%20to%20recognise%20the%20date%20as%20the%20UK%20format%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20be%20grateful%20for%20any%20assistance%20anyone%20can%20give.%26nbsp%3B%20Thanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-270383%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-1401591%22%20slang%3D%22en-US%22%3ERe%3A%20Modern%20List%20View%20Formatting%20with%20UK%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1401591%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F190353%22%20target%3D%22_blank%22%3E%40Gareth%20Jayne%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDid%20you%20ever%20manage%20to%20resolve%20this%3F%20Facing%20the%20same%20issue%20and%20not%20sure%20how%20to%20proceed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1401950%22%20slang%3D%22en-US%22%3ERe%3A%20Modern%20List%20View%20Formatting%20with%20UK%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1401950%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F672315%22%20target%3D%22_blank%22%3E%40Dr01dy%3C%2FA%3E%26nbsp%3Byou%20can%20do%20this%20with%20the%20built-in%20view%20formatting%20to%20highlight%20items%20on%20or%20before%20today%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22viewFormat-BeforeToday.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F192975iC0624A4B90AAC422%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22viewFormat-BeforeToday.png%22%20alt%3D%22viewFormat-BeforeToday.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1401996%22%20slang%3D%22en-US%22%3ERe%3A%20Modern%20List%20View%20Formatting%20with%20UK%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1401996%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40Rob%20Elliott%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20still%20seems%20to%20treat%20the%20today%20as%20an%20american%20date.%20See%20screen%20cap%20below%20where%20dates%20that%20fit%20the%20next%20review%20date%20is%20after%20today%20criteria%20do%20not%20get%20their%20background%20changed%20to%20green.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Capture.PNG%22%20style%3D%22width%3A%20282px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F192982i51BA401682A20937%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Capture.PNG%22%20alt%3D%22Capture.PNG%22%20%2F%3E%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-1402007%22%20slang%3D%22en-US%22%3ERe%3A%20Modern%20List%20View%20Formatting%20with%20UK%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1402007%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F672315%22%20target%3D%22_blank%22%3E%40Dr01dy%3C%2FA%3E%26nbsp%3Bmy%20screenshot%20showed%20the%20formatting%20where%20everything%20was%20in%20UK%20date%20format.%20So%20I'm%20not%20sure%20why%20it%20would%20be%20different%20for%20you%20unless%20something%20in%20your%20regional%20settings%20is%20not%20set%20correctly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1402079%22%20slang%3D%22en-US%22%3ERe%3A%20Modern%20List%20View%20Formatting%20with%20UK%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1402079%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40Rob%20Elliott%3C%2FA%3EThese%20are%20my%20region%20settings%20for%20the%20site.%20As%20far%20as%20I%20am%20aware%20this%20is%20correct.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Capture.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F192984i314A94BA59BB0485%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Capture.PNG%22%20alt%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi everyone

 

I'm trying to use the new view formatting in the Modern UI lists and am coming up against an issue with the date format.  I want to colour all rows red where the Due Date column value is equal to or past today's date using the following code:

 

{ "schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json", "additionalRowClass": "=if([$Due_x0020_Date] <= @now && [$Status] == '1 - Open', 'sp-field-severity--severeWarning', '')" }

 

My sites are UK-based so the date format is dd/mm/yyyy.  However when the formatting is being applied it seems to be looking at the date in the US format of mm/dd/yyyy.  With the UK format set for my site the item with a due date of 11/10/2018 doesn't appear as red:

 

UK Format.PNG

 

However, if I change the site settings to use the US date format it recognises that this date (11th October) has passed and colours the row red:

 

US Format.PNG

As my site date settings need to stay as UK format, is there a way I can add any code to my formatting to force it to recognise the date as the UK format?

 

I'd be grateful for any assistance anyone can give.  Thanks in advance.

 

5 Replies
Highlighted

@Gareth Jayne 

Did you ever manage to resolve this? Facing the same issue and not sure how to proceed.

Highlighted

@Dr01dy you can do this with the built-in view formatting to highlight items on or before today:

viewFormat-BeforeToday.png

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Highlighted

@Rob Elliott 

This still seems to treat the today as an american date. See screen cap below where dates that fit the next review date is after today criteria do not get their background changed to green.

Capture.PNG

 

 

Highlighted

@Dr01dy my screenshot showed the formatting where everything was in UK date format. So I'm not sure why it would be different for you unless something in your regional settings is not set correctly.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Highlighted

@Rob ElliottThese are my region settings for the site. As far as I am aware this is correct.

 

Capture.PNG