Date format change using substring

[KaterinaSimerdova]

Hello

I have dates in format DD/MM/YYY MIN:SEC

I need to change them to YYYY/MM/DD MIN:SEC.

But if I use the substring formula, Sec and Mins disappear.. 

Could someone please help?

Thank you!

[alexnajm]

Your Formula doesn't contain the logic needed to get the last part - please add a similar SUBSTRING function to your current formula like SUBSTRING([Update Time], 11,5) 

Also shouldn't you be referencing the other column instead of [Update Time]?

[KaterinaSimerdova]

Thank you @alexnajm , it worked! But now I have another problem.. 

When I tried to change the the Data Type for that column from String to DateTime, I have 'null' results..

I even tried to separate mins and secs with the same logic as year, mnth and day, but it didn't help either..

[KaterinaSimerdova]

second screen shot when changed to DateTime..

[alexnajm · Accepted solution]

Instead of overwriting the UPDATE TIME column with your logic, how about you create a new Update_time field since you are doing that anyways? that way you can set the data type right there

In that case, you can surround your statement with ToDatetime(). Also you could redo the entire statement using DateTimeParse() instead of all the substrings you have

[KaterinaSimerdova]

Thank you, Alex, for looking into this! I tried several ways, but every time the return value is null...:

• DateTime Tool
• ToDateTime
• DateTimeParse

[KaterinaSimerdova]

Hello @alex, thank you again for showing me different ways of handling this.

I tried with just one excel and it worked..

There must be an error in the different excel sheets within the folder that I load..

[alexnajm]

Very strange - glad it ended up working though for the other!