Format Salesforce Date Time fields in Excel

11:31 PM

So, i did a data export from salesforce.com and wanted to get the month from a date time field...


The general format of DateTime fields is like this 2012-01-15T18:34:00.000Z


If suppose you have this in column D in excel, just type in the following formula in an adjacent column

Say you do it in E1

=SUBSTITUTE(SUBSTITUTE(D1,”T”,” “),”.000Z”,”")

This eliminates the time part and you just get the date value....

Now, simply double-click on the bottom edge of E1 and the formula would be applied to the entire column E.


Now, E might still represent some number... Select the entire E column, right click and select "Format Cells" and select the category as "Date"...


To fetch the Day, Month and Year from column E, use the formulas = DAY(E1), =MONTH(E1), =YEAR(E1) ... 

2 comments

  1. Good trick. Using Excel Connector to pull data from Salesforce makes this sweeter.

    ReplyDelete
    Replies
    1. Very helpful. Thanks!

      Delete

AD