How to Convert date/time value to a date value (remove time portion) in Excel

Question – How to Convert date/time value to a date value (remove time portion) in Excel.
Scenario – Running short of time
Task – Need to push the data in Salesforce.com through the help of Data Loader.
Error Message – Error converting value to correct data type: Date Conversion FAILED.

MS Excel Error

Solution – Here we have a date/time value in cell A1 and we’d like to remove the time portion from this value. In cell B1, we’ve entered the following formula:
=MONTH(A1) & “/” & DAY(A1) & “/” & YEAR(A1)

Then paste the value (using paste special) in next cell. (See the screenshot below) –

Paste Special

Advertisements

About smattey

Sumit Mattey is the Leader, Cloud Technologist & Evangelist, Husband, Father and Fun Loving person. Currently working as - PROJECT MANAGER, R Systems Int'l Ltd.(Salesforce) Not simply defined by his career, Sumit lives with his family (whom he loves so much). He is an avid movie buff, a big book worm, a lover of food and beverage. While his schedule has not been forgiving as he'd like, he also enjoys doing photography with great outdoors and tries to hunt as often as possible.
This entry was posted in Cloud Computing, Salesforce.com and tagged , . Bookmark the permalink.

4 Responses to How to Convert date/time value to a date value (remove time portion) in Excel

  1. Sumit Mattey says:

    After this run the following formula
    =TEXT(A1,”yyyy-mm-ddThh:mm:ss”)&”.000Z”

    • Sumit says:

      =TEXT(A2,”yyyy-mm-ddThh:mm:ss”)&”.000Z”

      Above formula will decrease the date with one day.
      Example –
      If the date is 10/23/2010 – then it will insert the date as 10/22/2010.

  2. Denise says:

    Great, worked perfectly!!

  3. Pingback: priligy cialis

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s