Excel file as an HTML table and Date formatting

If you create an Excel file using an HTML Table, then it exists a way to format the cells based on some CSS rules. Some examples can be found on this blog post.

I spent some time trying to figure out how to display correctly the dates, whatever your regional settings are ; for exemple in the USA the format is MM/dd/YYYY, and for the rest of the world is dd/MM/YYYY.

Finally I found that the content of the date cells must always be defined with the same format : YYY-MM-dd HH:mm, and then we can apply a style like mso-number-format:"dd\-mmm\-yyyy\ hh\:mm".

Example:

<html>
<head>
<style>br {mso-data-placement:same-cell} .xls-date {mso-number-format:"dd-mmm-yyyy hh:mm"}</style>
</head>
<body>
  <table>
    <tr>
      <th>Event Name</th>
      <th>Event Date</th>
    </tr>
    <tr>
      <td>Birthday Party</td>
      <td class="xls-date">2015-11-01 10:00</td>
    </tr>
  </table>
</html>

So the date cell will display: 01-Nov-2015 10:00 and will be interpreted as a Date cell by Excel.

Laisser un commentaire