Formatting datetime

Massimo Minato

Good morning

I have a cube with a long date registred on it  with US  format 1/29/2018 3:50:28 PM

it is possible format a screen with this UE format  29/01/2018 15:50

 

Massimo

Björn Reuber

Hi

 

I assume, the long date field is saved in a board text field.

I think the fastest way would be to use NEXEL, seperate the string in different substrings (one for DD, one for MM, etc) and use the nexel Date and Time function .

 

Maybe this will help

 

regards

Björn

Samuele Marchetto

As alternative to Nexel, you could create another text cube, export dates from the first one and reload via ASCII Datareader using ETL by which you can apply some transformations with Excel formulas. If we suppose that your text US data is in first field of the file (in ETL the cell is C3), the formula that transform the date is:

 

=RIGHT("00"&MID(C3,FIND("/",C3)+1,FIND("/",C3,FIND("/",C3)+1)-FIND("/",C3)-1),2)&"/"&RIGHT("00"&LEFT(C3,FIND("/",C3)-1),2)&"/"&MID(C3,FIND("/",C3,FIND("/",C3)+1)+1,FIND(" ",C3)-FIND("/",C3,FIND("/",C3)+1)-1)&" "&IF(RIGHT(C3,2)="AM",MID(C3,FIND(" ",C3)+1,FIND(":",C3)-FIND(" ",C3)-1),MID(C3,FIND(" ",C3)+1,FIND(":",C3)-FIND(" ",C3)-1)+12)&":"&MID(C3,FIND(":",C3)+1,FIND(":",C3,FIND(":",C3)+1)-FIND(":",C3)-1)

Pietro Castelli

Hello,

as alternative to NEXEL, if the date has been stored as text you could also use a column algorithm with left(), right() or mid() formulas to parse day, month, year or hours, minutes and seconds. After you have parsed the different elements of the date you can then re-arrange the format as per your liking.

 

You can read this section of the manual for further information on the formulas:

http://help.board.com/Chapter_7_-_Capsules_files/The_Layout_object/Layout_Data_Blocks/Column_Algorithm.htm

 

Hope this helps

 

Pietro

Massimo Minato

I found this NEXEL Formula handy for me and it could be great to have also in the column algorithm:

=TEXT(DateValue([@a;*;*]);"yyyy-MM-dd HH:mm:ss")

 

Massimo