# Difficulty with calculated column



## nmss18 (May 30, 2012)

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 (May 30, 2012)

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


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


----------



## nmss18 (May 31, 2012)

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 (May 31, 2012)

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 (May 31, 2012)

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 (May 31, 2012)

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 (May 31, 2012)

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


----------



## ruve1k (May 31, 2012)

nmss18 said:


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


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



nmss18 said:


> What is the LEN formula?


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


----------



## nmss18 (Jun 4, 2012)

ruve1k said:


> 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. 




ruve1k said:


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


it indicates I have 23 charachters on this field


----------



## ruve1k (Jun 4, 2012)

Very strange. DATEVALUE worked fine for me.


----------



## nmss18 (May 30, 2012)

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 -->


----------



## masplin (Jun 5, 2012)

Silly question but is the length 23 for every entry? You only need one to be differnet and you get an error on the whole column. Just click the table heading to filter and see if only one value.  I've had similar issues and found one entry was rogue.  I think when this happens the error box shows #Error and also "show first error" or similar wording which also indicates its not a formaula error but an input data error and will take you to the first offending field.

Mike


----------

