Forum Discussion
Column Format - Time!
Good Afternoon
I'm trying to format a DateTime field with a bit of verve. I got JSON from GitHub but I need to modify as I want to include the time. - The only example that includes what I'm looking for is this:
https://github.com/pnp/List-Formatting/tree/master/column-samples/generic-world-time
But I can't get it quite to work and I don't understand why.
This is what I'm aiming for but the time represented is 21 June - 2:30 pm! I'm 14 hours out (Regional Settings are Australia, UTC + 10)
As the original code comes from something that does a time conversion, I thought to take out the bit that adds the conversion factor. I have tried removing "+Number([$UTC]" (and why isn't it screaming at me anyway for including a reference to a non-existing field?), adding 0, multiplying by one ...
"elmType": "div",
"children": [
{
"elmType": "span",
"txtContent": "=padStart(toString(floor(((Number(@currentField)+Number([$UTC])*3600000)%86400000)/3600000)),2,'0')"
},
{
"elmType": "span",
"txtContent": ":"
},
{
"elmType": "span",
"txtContent": "=padStart(toString(floor(((Number(@currentField)+Number([$UTC])*3600000)%86400000)%3600000/60000)),2,'0')"
}
]
They all result in something like this:
It's all a foreign language to me but I've worked out:
- padStart pads the string at the beginning with 0 to a length of 2 - won't actually need that for the hour, happy with 1:30 pm
- Number I'm guessing turns the date time string into a number which we can then manipulate
- floor does some rounding, not sure why that's needed
- toString turns it back into a string for display, I imagine
But I don't understand why it would need the UTC calculation. And I think it's the UTC calculation that messes with my times.
If anybody understands this or can point me to some documentation that will help me understand, I would appreciate it.
Thanks
Christine
I was working further on this. I think the UTC time adjustment might be useful depending on what one wants to achieve.
I was working on a workaround using to toLocaleTimeString (top band of view) and it displays the Aussie time (see Test Date) as +2. I'm in New Zealand. So I think it's working to the locale on my computer because the locale in the group is Australia. (I have yet to schedule time with a colleague in Australia to see what they see!) - Displaying things in people's local time clearly could have its uses.
Here's the code but note there's some rubbish in there as I'm trying to keep track of things:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "style": { "flex-direction": "column", "width": "60px", "margin": "5px", "display": "=if(@currentField,'flex','none')" }, "children": [ { "elmType": "div", "title": "Displays month", "txtContent": "=if(indexOf(toLocaleTimeString(@currentField) + '^', '^') == 10,substring(toLocaleTimeString(@currentField),0,4),substring(toLocaleTimeString(@currentField),0,5)) + ' ' + if(endsWith(toLocaleTimeString(@currentField),'am') == 'true','am','pm')", "description": "=if([$State]=='WA','COB',if([$State]=='NZ',12,if([$State]=='SA','COB',if([$State]=='QLD',0,0))))", "style": { "display": "flex", "justify-content": "center", "align-items": "center", "width": "100%", "height": "20px", "border": "1px solid", "background-color": "#ec6602", "margin-bottom": "-1px" }, "attributes": { "class": "ms-fontColor-white ms-borderColor-themePrimary ms-fontWeight-bold" } }, { "elmType": "div", "description": "centre portion needs to display weekday and date", "style": { "display": "flex", "justify-content": "center", "align-items": "center", "width": "58px", "border": "1px solid", "border-color": "#ec6602", "color": "#ec6602", "height": "30px" }, "children": [ { "elmType": "span", "description": "Displays weekday", "forEach": "weekday in split('0,1,2,3,4,5,6',',')", "style": { "white-space": "nowrap" }, "attributes": { "class": "ms-fontWeight-bold" }, "children": [ { "elmType": "span", "txtContent": "=if([$weekday]=='0','Sat',if([$weekday]=='1','Sun',if([$weekday]=='2','Mon',if([$weekday]=='3','Tue',if([$weekday]=='4','Wed',if([$weekday]=='5','Thu',if([$weekday]=='6','Fri',''))))))", "style": { "padding": "1px", "display": "=if((getMonth(@currentField)+1)>2,if(((getDate(@currentField)+floor(26*(getMonth(@currentField)+1+1)/10)+Number(substring(toString(getYear(@currentField)),2,4))+floor(Number(substring(toString(getYear(@currentField)),2,4))/4)+floor(Number(substring(toString(getYear(@currentField)),0,2))/4)-2*Number(substring(toString(getYear(@currentField)),0,2)))%7)==Number([$weekday]),'','none'),if(((getDate(@currentField)+floor(26*(getMonth(@currentField)+1+12+1)/10)+Number(substring(toString(getYear(@currentField)-1),2,4))+floor(Number(substring(toString(getYear(@currentField)-1),2,4))/4)+floor(Number(substring(toString(getYear(@currentField)-1),0,2))/4)-2*Number(substring(toString(getYear(@currentField)-1),0,2)))%7)==Number([$weekday]),'','none')" } } ] }, { "elmType": "span", "description": "Displays date", "txtContent": "=getDate(@currentField)", "style": { "padding": "1px" } } ], "attributes": { "class": "ms-fontWeight-bold ms-fontSize-14" } }, { "elmType": "div", "description": "Displays time - time zone issue", "children": [ { "elmType": "span", "txtContent": "=padStart(toString(floor(((Number(@currentField)+Number([$UTC])*3600000)%86400000)/3600000)),2,'0')", "options": "hour: 'numeric', dayPeriod: 'short'", "Description": "Intl.DateTimeFormat('en-US',options).format(@currentField)" }, { "elmType": "span", "txtContent": ":" }, { "elmType": "span", "txtContent": "=padStart(toString(floor(((Number(@currentField)+Number([$UTC])*3600000)%86400000)%3600000/60000)),2,'0')" } ], "style": { "display": "flex", "justify-content": "center", "align-items": "center", "width": "100%", "height": "20px", "border": "1px solid", "background-color": "#ec6602", "margin-bottom": "-1px" }, "attributes": { "class": "ms-fontColor-white ms-borderColor-themePrimary ms-fontWeight-bold" } } ] }I'll post anything new that comes to light.
Christine
