Convert Date to Text without losing the format in Excel?

Question

I have a column in Excel Sheet which contains all the dates in custom dd-mm-yyyy format. I need to convert all these dates to text without losing the format. If I am changing the cell format to Text, I am losing the format. I tried to copy the cell values as values but did not work. I searched a lot on the internet, but did not find any useful resource. What's the possible solution?

Answer 1

Try using the TEXT function.

=TEXT(A1,"dd-mm-yyyy")

Answer 2

Use this formula for keeping the long date format from "A1" cell in another cell (exp: "B1"):

=TEXT(A1,"[$-F800]dddd, mmmm dd, yyyy")

The cell "A1" is a cell contains a date (such as today date with «today()» formula) with long date format.

Also, you can use this VBA code for getting same format with specify font and size in print's header: ActiveSheet.PageSetup.RightHeader = "&""Arial Rounded MT Bold,Regular""&16" & Range("B1").Value

This code will shows the "B1" cell (as a text from "A1" cell) in Right of Header in Print.