Forum Discussion

Amapola67's avatar
Amapola67
Copper Contributor
Jun 06, 2024

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

 

SharePoint Online
  • Amapola67's avatar
    Amapola67
    Copper Contributor
    Jun 09, 2024

    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