Difficulty with calculated column

[nmss18]

Hello,
I have a column with date & time data in this format:
2012-02-01 00:12:05.637
I need to separate the date from the time, and for the time, I just need the hours,minutes,seconds. Sor using the example above, I want to create a calculated column with the time data to look like this:
00:12:05

Any assiatance would be appreciated.
Thanks,
Nathan
<!-- / message --><!-- edit note -->

 

[ruve1k]

Is the DateTime column stored as text or as a date?
I.e. If you format it as general does it look like

1. 2012-02-01 00:12:05.637 (this would be text) or
2. 40940.0083985764 (this would be a date)

 

[nmss18]

Thanks for responding.
The date column is formatted as text and 'text' is the only option I have to choose from in the drop down menu for 'formatting'.
I also have another drop down menu for 'data type, which does give me 6 options to choose from including 'date'. However, when I highlight the column and choose it, I get a 'Failed to change column data type' popup error with the following details:
============================
Error Message:
============================
The following system error occurred: Type mismatch.
PowerPivot database error: Datatype conversion failed for [Table: 'TalmonLog', Column: '', Value: '2012-02-01 00:10:00.660'].
----------------------------
Failed to change column data type.

 

[ruve1k]

Try this:
Date:

=DATEVALUE(LEFT([Column1],10))

Then format the column with the custom date format that corresponds to yyyy-mm-dd

Time:

=TIMEVALUE(MID([Column1],12,8))

Then format the column with the custom date format that corresponds to HH:mm:ss

 

[nmss18]

I am getting an #ERROR on both formulas.
"The following system error occurred: Type mismatch. "

I double checked to ensure, i was specifying the proper columns and entering the proper syntax.
This is the syntax I entered:
=DATEVALUE(LEFT(TalmonLog[Event Time],10))

I see now in the calculated column, that the 'formatting' drop down menu has a large selection of different date format types but choosing anyone doesnt alter anything on the calculated column to remove the #ERROR.

-Nathan

 

[ruve1k]

What is returned if you just use the formula below?
=LEFT(TalmonLog[Event Time],10)

There may be some non-visible characters that are causing trouble.

Also, to help trouble-shoot you may want to check LEN(TalmonLog[Event Time]) to ensure that it returns what you expect. (It should be 23)

 

[nmss18]

Thanks, I actually tried that before and it did work.
What is the LEN formula?

 

[ruve1k]

Thanks, I actually tried that before and it did work.

Yes, but it'll only be text; not a proper date.

What is the LEN formula?

It measures the LENGTH of the text string. It returns the number of characters.

 

[nmss18]

Yes, but it'll only be text; not a proper date.

You are right. It is still in text format. The 'Formatting' drop down still does not give me any other option than 'text'. Also, i tried using the DATEVALUE just on this column as well and got an error.

It measures the LENGTH of the text string. It returns the number of characters.

it indicates I have 23 charachters on this field

 

[ruve1k]

Very strange. DATEVALUE worked fine for me.