Gantt Chart column view - can you add another formula to allow dd-mm-yyyy

[NessyNoodlles]

4ef2644

With the Gantt chart column formula, it only allows for two types of date formatting and I need to have it dd-mm-yyyy to match our region. I did set dd/MM/yyyy up in the formula but the result wasn't accurate and it actually messed up my dates (changed the dates to incorrect ones). My sharepoint site has the region set so the natural date format is dd/mm/yyyy.

What Actually Happens

1. imported data from excel with date format dd/mm/yyyy
2. Checked data and configured columns to match Start and End with no timing.
3. Added the new column based on the settings shown and returned to the list. The dates have been changed. The gantt view shows the current date as sometime in 2024 and there is no shading for the period of the event.

I've added some images below to show you what happens before and after. If you could assist with this, I would be very appreciative as it will solve a big problem for me! Thank you for your assistance.

[NessyNoodlles]

I should also add, that I deleted the list and recreated it to see if it re-occurred and it did.

[tecchan1107]

Hi @NessyNoodlles . Thanks for the screenshots! They are very helpful!

I changed the locale of the site to dd/MM/yyyy as a test, and the Gantt chart was displayed.

Perhaps, but the formula is incorrect and the Gantt chart is not displayed.

I am very sorry, but the Gantt chart can only be displayed by the formulas written in the README.
*Please use the following formulas even if your site displays dates in dd/MM/yyyy format.

However, in either formula, the start and end shown on the Gantt chart will not be displayed in dd/MM/yyyy.

If you want the start and end dates to also be displayed in dd/MM/yyyyy, try using Formula2 (yyyy-MM-dd) and use the following code.

Sample Code (Click to open/close)

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "elmType": "div",
      "style": {
        "display": "flex",
        "flex-direction": "row",
        "width": "100%",
        "align-items": "center"
      },
      "children": [
        {
          "elmType": "div",
          "style": {
            "display": "flex",
            "flex-direction": "column",
            "width": "100%",
            "margin-bottom": "3px"
          },
          "children": [
            {
              "elmType": "div",
              "style": {
                "display": "flex",
                "justify-content": "space-between",
                "width": "100%",
                "white-space": "nowrap"
              },
              "attributes": {
                "class": "ms-fontSize-xs"
              },
              "children": [
                {
                  "elmType": "div",
                  "style": {
                    "display": "flex",
                    "flex-direction": "row-reverse"
                  },
                  "children": [
                    {
                      "elmType": "div",
                      "txtContent": "=if(loopIndex('_startdate')>1,'','/')+[$_startdate]",
                      "forEach": "_startdate in split(substring(@currentField,indexOf(@currentField,'(')+1,indexOf(@currentField,'_')),'-')"
                    }
                  ]
                },
                {
                  "elmType": "div",
                  "style": {
                    "display": "flex",
                    "flex-direction": "row-reverse"
                  },
                  "children": [
                    {
                      "elmType": "div",
                      "txtContent": "=if(loopIndex('_enddate')>1,'','/')+[$_enddate]",
                      "forEach": "_enddate in split(substring(@currentField,indexOf(@currentField,'_')+1,indexOf(@currentField,')')),'-')"
                    }
                  ]
                }
              ]
            },
            {
              "elmType": "div",
              "style": {
                "width": "100%",
                "border": "1px solid",
                "height": "13px",
                "position": "relative",
                "overflow": "hidden"
              },
              "children": [
                {
                  "elmType": "div",
                  "style": {
                    "position": "absolute",
                    "height": "100%",
                    "width": "=((Number(Date(substring(@currentField,indexOf(@currentField,'^')+1,indexOf(@currentField,'('))))-Number(Date(substring(@currentField,0,indexOf(@currentField,'^'))))+86400000)/(Number(Date(substring(@currentField,indexOf(@currentField,'_')+1,indexOf(@currentField,')'))))-Number(Date(substring(@currentField,indexOf(@currentField,'(')+1,indexOf(@currentField,'_'))))+86400000))*100+'%'",
                    "left": "=((Number(Date(substring(@currentField,0,indexOf(@currentField,'^'))))-Number(Date(substring(@currentField,indexOf(@currentField,'(')+1,indexOf(@currentField,'_')))))/(Number(Date(substring(@currentField,indexOf(@currentField,'_')+1,indexOf(@currentField,')'))))-Number(Date(substring(@currentField,indexOf(@currentField,'(')+1,indexOf(@currentField,'_'))))+86400000))*100+'%'"
                  },
                  "attributes": {
                    "class": "ms-bgColor-themePrimary",
                    "title": "=[$Start.displayValue]+' ～ '+[$End.displayValue]"
                  }
                },
                {
                  "elmType": "div",
                  "style": {
                    "position": "absolute",
                    "height": "100%",
                    "width": "=(86400000/(Number(Date(substring(@currentField,indexOf(@currentField,'_')+1,indexOf(@currentField,')'))))-Number(Date(substring(@currentField,indexOf(@currentField,'(')+1,indexOf(@currentField,'_'))))+86400000))*100+'%'",
                    "left": "=((Number(@now)-(60*60*1000*12)-Number(Date(substring(@currentField,indexOf(@currentField,'(')+1,indexOf(@currentField,'_')))))/(Number(Date(substring(@currentField,indexOf(@currentField,'_')+1,indexOf(@currentField,')'))))-Number(Date(substring(@currentField,indexOf(@currentField,'(')+1,indexOf(@currentField,'_'))))+86400000))*100+'%'",
                    "display": "flex",
                    "justify-content": "center",
                    "align-items": "center"
                  },
                  "children": [
                    {
                      "elmType": "div",
                      "style": {
                        "width": "100%",
                        "min-width": "5px",
                        "height": "100%"
                      },
                      "attributes": {
                        "class": "ms-bgColor-themeLight",
                        "title": "Now"
                      }
                    }
                  ]
                }
              ]
            }
          ]
        },
        {
          "elmType": "div",
          "style": {
            "padding": "10px",
            "margin-left": "5px",
            "cursor": "pointer",
            "border-radius": "50%"
          },
          "attributes": {
            "iconName": "Edit",
            "class": "ms-bgColor-themeLighter--hover"
          },
          "customCardProps": {
            "openOnEvent": "click",
            "directionalHint": "topCenter",
            "isBeakVisible": true,
            "formatter": {
              "elmType": "div",
              "style": {
                "display": "flex",
                "flex-direction": "column",
                "padding": "10px 15px"
              },
              "children": [
                {
                  "elmType": "div",
                  "style": {
                    "display": "flex",
                    "flex-direction": "row",
                    "align-items": "center"
                  },
                  "children": [
                    {
                      "elmType": "div",
                      "inlineEditField": "[$Start]",
                      "style": {
                        "border": "1px solid",
                        "padding": "5px 10px",
                        "border-radius": "3px",
                        "display": "flex",
                        "align-items": "center",
                        "margin": "5px"
                      },
                      "children": [
                        {
                          "elmType": "span",
                          "txtContent": "[$Start.displayValue]"
                        },
                        {
                          "elmType": "span",
                          "style": {
                            "margin-left": "8px"
                          },
                          "attributes": {
                            "iconName": "Edit"
                          }
                        }
                      ]
                    },
                    {
                      "elmType": "div",
                      "txtContent": "～",
                      "style": {
                        "margin": "5px"
                      }
                    },
                    {
                      "elmType": "div",
                      "inlineEditField": "[$End]",
                      "style": {
                        "border": "1px solid",
                        "padding": "5px 10px",
                        "border-radius": "3px",
                        "display": "flex",
                        "align-items": "center",
                        "margin": "5px"
                      },
                      "children": [
                        {
                          "elmType": "span",
                          "txtContent": "[$End.displayValue]"
                        },
                        {
                          "elmType": "span",
                          "style": {
                            "margin-left": "8px"
                          },
                          "attributes": {
                            "iconName": "Edit"
                          }
                        }
                      ]
                    }
                  ]
                },
                {
                  "elmType": "div",
                  "txtContent": "If you changed the date manually instead of using the calendar, press [Enter] after changing the date.",
                  "style": {
                    "max-width": "300px",
                    "text-align": "center"
                  },
                  "attributes": {
                    "class": "ms-fontSize-s"
                  }
                }
              ]
            }
          }
        }
      ]
    }
  ]
}

[tecchan1107]

I am sorry. I feel I did not answer your question.

can you add another formula to allow dd-mm-yyyy

Unfortunately, it is difficult to add the dd-MM-yyyy format to a formula. The reason is that I use the Date operator in the Gantt chart, and if you enter a date in dd-MM-yyyy or dd/MM/yyyy format in the Date operator, it will not be recognized as a date and will display "Invalid Date". (I have yet to figure out why this occurs)

So, please use the yyyy-MM-dd format in your formula.

[tecchan1107]

Since there seems to be no reply, I will close it. If you have any questions, sorry, but please create a new issue again.