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
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
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);
}
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
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)
};
}]);
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