Order by modified date on a text calculated column

Question

Due to a business requirement, the dates need to be displayed using the following format: DD MMM YYYY HH:MM

Because I am converting modified date to text using the following formula on a calculated column: =TEXT(Modified,"dd mmm yyyy hh:mm"). When I want order the column ascending or descending the result is based on the text and I cannot order by dates any more.

How can I order by modified date when ordering this calculated column?

Ideally I don’t have to have another column with the standard SharePoint modified date column to be able to order by date.

I am using SharePoint 2016 on premise, but I think that the answer would be valid for other versions.

An example would be great. Thanks a lot.

Answer 1

You can use the CSR to change the display format of list column.

• Edit your list view web part properties.
• Under Miscellaneous section you will see "JS Link"
• You can enter the path of JS file.

You can use the following code in a JS file update the path under JS Link property

(function () {
    var requestCtx = {};
    requestCtx.Templates = {};

    requestCtx.Templates.Fields = {
        'Modified': {'View': modifiedFieldTemplate}   
    };

    SPClientTemplates.TemplateManager.RegisterTemplateOverrides(requestCtx);
})();

function modifiedFieldTemplate(ctx) {
    var date = new Date(ctx.CurrentItem.Modifed).format("dd MMM yyyy hh:mm");   
    return date;
}

Answer 2

If I understand correctly, I think all you need to do is to ensure that the data type returned from this formula should be data/time.

Set the Data Type to Date/Time

Result