Format Serial Number to Date-Time Format

Canvas Canvas Questions
1

Hello All

We currently have a serial number stored in a cube for a timestamp (Excel-based date-time number). Is there any way in HTML to convert this number to a date-time string for display purposes, e.g. yyyy-mm-dd hh:mm:ss?

Thanks
David

2

You can try to use angular $filter for that, or you may create your own $filter

{{ date_expression | date : format : timezone}}

function to convert excel date:

function getJsDateFromExcel(excelDate) {

  // JavaScript dates can be constructed by passing milliseconds
  // since the Unix epoch (January 1, 1970) example: new Date(12312512312);

  // 1. Subtract number of days between Jan 1, 1900 and Jan 1, 1970, plus 1 (Google "excel leap year bug")             
  // 2. Convert to milliseconds.

	return new Date((excelDate - (25567 + 1))*86400*1000);

}
3

Thanks Eugene, I was able to get the date using the suggestions above.

Unfortunately for us, we’ve complicated matters by including decimals for time, e.g. 43028.6090046296 for 2017-10-20 14:36:58. Rather than complicate the HTML code I think we’re better off rethinking how we store the value in TM1 to make it easier to display in Canvas.

Cheers
David

4

if you don’t want to create a custom filter, you may use the function I proposed above only.

HTML

<div ng-controller="HomeCtrl">
  <h1><i class="fa fa-home"></i> {{'HOME' | translate}}</h1>
  
  <hr />


  <br>
 <dir class="row">
   <div class="col-md-6">
  <table class="table table-bordered">
      <tr>
        <td>
            {{someDate}}
        </td>
        <td>  {{transformExcelDate(someDate) | date : "yyyy-MM-dd hh:mm:ss" : 0}} </td> 
      </tr>
  
    </table>
   </div>
 </dir>

</div>

JS

app.controller('HomeCtrl', ['$scope', '$rootScope', '$interval', '$timeout', '$state', '$stateParams', '$http', '$filter',
	function ($scope, $rootScope, $interval, $timeout, $state, $stateParams, $http, $filter) {



		$scope.someDate = 43028.6090046296;
		$scope.transformExcelDate = function (excelDate) {

			// JavaScript dates can be constructed by passing milliseconds
			// since the Unix epoch (January 1, 1970) example: new Date(12312512312);

			// 1. Subtract number of days between Jan 1, 1900 and Jan 1, 1970, plus 1 (Google "excel leap year bug")             
			// 2. Convert to milliseconds.

			return new Date((excelDate - (25567 + 1))*86400*1000);
                     // or this:
			//return new Date(1900, 0, --excelDate)
			
		};



	}]);
1 Like
5

Hi Eugene

After I replied the first time I realised I was doing my math wrong and it is simple to convert even decimals to milliseconds like you said!

I have used your function, so simple. Thanks so much for your help.

Cheers
David