Question

How can i convert date from dd.mm.yy to yyyy (4 digit only ) in column in excel


Badge +3

How can i convert date from dd.mm.yy to yyyy (4 digit only ) in column in excel

 

as example 01.02.2000 to 2000 only

Thanks


12 replies

Userlevel 2
Badge +12

Have a look at the DateTimeConverter transformer.

Badge +5

Could you not also use a text string function to strip the preceding characters, retaining just the last 4 digits?

Badge +3

Have a look at the DateTimeConverter transformer.

 

 

 

i found the problem ,it reads last examination as char but it is in column date as 20.06.2019

do you have suggestion to convert from char to date .

i tried to convert char type to date type manual of the reader but it does not work also but i need a way to do that automatically in correct way bcs all my excel files have the same issues .thanks

Badge +3

Could you not also use a text string function to strip the preceding characters, retaining just the last 4 digits?

what u mean about string function ,i do not have transformer in fme 2018 for that .thanks

i think ,this is my problem bcs i did another column called date ,it shows that type is date and i get output but old colzumn that what i need last examination ,the type is char so i did not get any output.i need to convert from char type to date automatically .thanks

Badge +2

@soly DateTimeCOnverter should work. Try an input format string of %d.%m.%Y$

Userlevel 2
Badge +12

Have a look at the DateTimeConverter transformer.

If it is a Char where the year is always the last 4 characters, the SubstringExtractor transformer (from -4 to -1) will result in getting the year.

Badge +3

If it is a Char where the year is always the last 4 characters, the SubstringExtractor transformer (from -4 to -1) will result in getting the year.

thansk for reply i got ur idea but i checked the output on inspector and it is the same as input no change

input excel file

 

i choose source string the column that i need

 

outüut inspector is still the same ,

do u have another solution or what do u think which wrong or do we have way to convert tye attribute .thanks

 

 

 

 

Badge +3

@soly DateTimeCOnverter should work. Try an input format string of %d.%m.%Y$

thanks but i did it and it does not work with me

 

could you provide me an example of work space if u have time but make the input as char for date and thanks i need only the last 4 digit only the year

Userlevel 2
Badge +12

thansk for reply i got ur idea but i checked the output on inspector and it is the same as input no change

input excel file

 

i choose source string the column that i need

 

outüut inspector is still the same ,

do u have another solution or what do u think which wrong or do we have way to convert tye attribute .thanks

 

 

 

 

End index should be -1 instead of 1

Badge +3

End index should be -1 instead of 1

m.xlsx

 

please this is the excel file.could u try by ur self and let me know,how can i convert last column that include char .i know ur idea by substring,it should be correct but it does not work with me .i do not know the reason and i changed the index and it does not work .thanks for helping

Userlevel 2
Badge +12

m.xlsx

 

please this is the excel file.could u try by ur self and let me know,how can i convert last column that include char .i know ur idea by substring,it should be correct but it does not work with me .i do not know the reason and i changed the index and it does not work .thanks for helping

Here is the solution:

And the workspace:

Date_to_Year.fmw

Hope this helps.

Badge +3

Here is the solution:

And the workspace:

Date_to_Year.fmw

Hope this helps.

thanks alot

Reply