# Remove time from date stamp



## General Ledger (Nov 17, 2014)

I have a table of transactions and each record has a date and time stamp.I don’t care about the time portion.Must I create a calculated column in order to get a field of transaction dates?Is there a way to truncate the date and time field before it comes into my table in PowerPivot?I don’t have the ability to make changes to the table in the database.  What is the best function to use to trim the time off a date field?  I am very new to PowerPivot and using Excel 2010.

Thanks,

G/L


----------



## Fowmy (Nov 17, 2014)

Hi 

I use below formula to separate the date and time from on a workshsheet.

To extract date =C4-MOD(C4,1) 

To extract time =MOD(C4,1) 

Hope this would be of any help to you.


----------



## GDRIII (Nov 17, 2014)

I mostly use =DATEVALUE(transactionstable[datetimestamp] in a calculated column once the data is in the PP window


----------



## scottsen (Nov 17, 2014)

If at all possible, get rid of the time BEFORE it gets to Power Pivot.  SQL is possible, otherwise... Power Query.


----------



## aviSingh (Nov 17, 2014)

G/L,

Scott has good advice - trim the Time portion off before it lands in Power Pivot (rather than a calculated column), mostly for performance reasons. You do not need to make changes to the table in order to do that. It wasn't clear what your data source is. If it is SQL, you can use CAST([_datecolumn_] as Date) to trim out the Time portion.

Avi
www.PowerPivotPro.com


----------

