Jan
16
2013

Using Concatenate function in excel without impacting custom format

Suppose you have date time text in one column (format as YYYY-MM-DD) which you want to use in your concatenate function without changing custom format, because if you don’t do it then in concatenate function it will come in some numerical form for e.g. 40513 instead of 2010-12-01. It happens because excel ignores formatting.

 

Use below to fix it,

Say column name is A1 where date value resides and you want to sue it in concatenate function present in another column, your concatenate function will look like,

=CONCATENATE(TEXT(A1,”YYYY-MM-DD”)

 

A more complex example is,

=CONCATENATE(A1,”‘”,TEXT(B1,”YYYY-MM-DD”),” 00:00:00.000′”,C1,”‘”,TEXT(D1,”YYYY-MM-DD”),” 00:00:00.000’; Go”)

 

Usage of this is limitless; you can keep any type of custom formatting using Text function.

 

Reference:

http://www.techrepublic.com/article/concatenate-date-cells-with-text-cells-in-excel/6137546

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 23 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

November 2017
M T W T F S S
« Oct    
 12345
6789101112
13141516171819
20212223242526
27282930  

View Post by Categories

%d bloggers like this: