Date Formatting issues when using a table

[reenieh]

I have created a worksheet that is linked to a sharepoint log. The "created date" column is formated as such: 11/7/2011 4:19:07 PM. I only need the date (for a pivot table), so i created a column at the end of the table and added this formula: =LEFT(Table_owssvr_1[[#This Row],[Created]],5). This returned the value of 40854 (11/7/2011). However, no matter what I try, it will not convert this number into a date format, "format cells" does not work as long as this value is in the table. If i try to format the number outside the table, it will format to a date. Any ideas?

 

[vaskov17]

=text(LEFT(Table_owssvr_1[[#This Row],[Created]],5),"mm/dd/yyyy")

 

[Smitty]

Welcome to the Board!

Try TEXT:

=TEXT([@Created],"mm/dd/yy")

HTH,

 

[Asala42]

I'd probably either change it to a direct cell reference which you can change the format:
=LEFT(A2,5)+0

or wrap a text function around it:
=TEXT(LEFT(Table_owssvr_1[[#This Row],[Created]],5),"mm/dd/yy")

 

[reenieh]

Thanks Vaskov17, that totally worked!!