How to clear date time value from a list using column formatting

Deleting a date column value in Microsoft Lists and SharePoint lists without using the item form is an impossible task as this column type needs to receive a date in a specific format and does not accept empty values.

With column formatting however it is possible to workaround this limitation and hide the date when it is not needed, in this article I’ll show you a possible solution for it.

clear date time from SharePoint lists and Microsoft Lists

This problem came to my attention after the a twitter post from てっちゃん (Tetsuya Kawahara), if you don’t follow him yet make sure you do, his work with list formatting will blow your mind.

In the following scenario there is a formatting action trying to remove the date when a task was not completed yet, however when doing it Microsoft Lists displays an error saying the value added to the date column is not in the correct format.

clear date time from SharePoint lists and Microsoft Lists

The generic formatting code causing this error is the following:

{
  "elmType": "div",
  "txtContent": "Remove date",
  "customRowAction": {
    "action": "setValue",
    "actionInput": {
      "Date": ""
    }
  }
}

How to remove a date value from a column

For scenarios where you really need the date to be removed from the column after it has been filled you can implement the workaround described below.

  1. Instead of wiping the date value with the action you need to define it to the earliest date value Microsoft Lists accept 1/1/1900
    {
      "elmType": "div",
      "txtContent": "Remove date",
      "customRowAction": {
        "action": "setValue",
        "actionInput": {
          "Date": "1/1/1900"
        }
      }
    }
    
  2. In the Date column you must apply a column formatting to hide the value if the year is equal to 1/1/1900
    {
      "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
      "elmType": "div",
      "txtContent": "=if(getYear(Date(@currentField)) == 1900, '', toLocaleDateString(Date(@currentField)))"
    }
    

In the following animation you can see how it works with the date being removed, if this solution does not work for your scenario let me know more details in the comment section and I’ll try to help you with a workaround.

clear date time from SharePoint lists and Microsoft Lists


No comments yet

Leave a Reply


I've been working with Microsoft Technologies over the last ten years, mainly focused on creating collaboration and productivity solutions that drive the adoption of Microsoft Modern Workplace.