SheetJS formatting a date column

Question

v 0.10.2 of sheets.js. I am trying to format a column in excel which is a date so that when filtered the filters shown by excel are date related ie they all filtering by days months and years.

I have formatted the excel created using the following to make the 5th column into a date one.

 range.s.c = 5; 
 range.e.c = 5;      //  end of the column
 XLSX.utils.sheet_set_range_style(ws, range, {
 z: "dd/mm/yyyy"
                });

The excel created is attached. As can be seen the format of "TransactionDate" column is "Date". But excel does not recognise it as a date as creating a filter on that column shows excel thinks its a text cell.

Is there anyway to make excel recognise that cell or column as an actual date one.

Harj

I first ended up with just a character field. I used the range and set the format to dd/mm/yyyy and that did the trick of cell being "seen" as a date in excel but it does not recognise it as a date.

Answer 1

I had to format the date field in Javascript. JSONData is the JSON data returned back from the server to the javascript app. This is multi-dimensional array so spinning through the data to get the date field and then formatting the date field to a javascript date object. The original JSONData is converted to NewJSONData.

for (var key in JSONData) {
     var value = JSONData[key];
                                    
     let obj={};
     for (var key2 in value) {
        var value2 = value[key2];
        
        /* TransactionDate is the date field */
        if (key2==="TransactionDate" && value2!==""){
                        
            /* DD/MM/YYYY is the format the date is in. 
               We want to convert it into MM/DD/YYYY */
                        
             xx=moment(value2,'DD/MM/YYYY').format('MM/DD/YYYY');
             obj[key2]=new Date(xx);
         }
         else{
             obj[key2]=value2
         }    
                    
     }
                
     NewJSONData.push(obj);
                
  }

  var ws = XLSX.utils.json_to_sheet(NewJSONData);