Convert date field into text in Excel [duplicate]

Question

I have an Excel file which has a column formatted as date in the format dd-mm-YYYY.

I need to convert that field to text. If I change the field type excel converts it to a strange value (like 40603).

I tried the text function but it gives me Error 508.

Any help?

What do you mean convert to text? You want to have your date appear in a different format? (40603 is the number of days since 1900) — assylias
– assylias, Commented Apr 3, 2012 at 12:06
I need to concatenate that date with other text in a formula I have. But when i select the field where the date is in the formula it doesn´t put the date(23-04-2012) but the converted value. I want it to keep the date as string. — brpaz
– brpaz, Commented Apr 3, 2012 at 12:11

barry houdini · Accepted Answer · 2012-04-03 12:20:56Z

69

You don't need to convert the original entry - you can use TEXT function in the concatenation formula, e.g. with date in A1 use a formula like this

="Today is "&TEXT(A1,"dd-mm-yyyy")

You can change the "dd-mm-yyyy" part as required

answered Apr 3, 2012 at 12:20

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

brpaz Over a year ago

I got error501 when applying =TEXT(D2,"dd-mm-yyyy") where D2 as the value 18-03-2011 formatted as a date

barry houdini Over a year ago

I don't know what that error means - depending on your location you might need semi-colon in place of comma like ="Today is "&TEXT(A1;"dd-mm-yyyy") - and TEXT function is also region-specific so you need to use exactly the same format in the TEXT function as you would in your formatting

Community · Accepted Answer · 2020-06-20 09:12:55Z

26

You can use TEXT like this as part of a concatenation

=TEXT(A1,"dd-mmm-yy") & " other string"

enter image description here

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 3, 2012 at 12:23

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Comments

AlexHalkin · Accepted Answer · 2012-04-03 12:10:34Z

18

If that is one table and have nothing to do with this - the simplest solution can be copy&paste to notepad then copy&paste back to excel :P

answered Apr 3, 2012 at 12:10

AlexHalkin

1,12716 silver badges34 bronze badges

2 Comments

AlexHalkin Over a year ago

just before pasting back to excel make the target cells formatted as text

Michele Over a year ago

Copy&Paste ... incredible! Thank you, you've saved me from throwing the pc out of the window!

Collectives™ on Stack Overflow

Convert date field into text in Excel [duplicate]

3 Answers 3

2 Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Linked

Related