CODEWITHSUNDEEP
excelexcel-formula
Tim Wilkinson
Tim Wilkinson

Reputation: 3791

Excel convert date to text retaining date layout

I have rows containing every Monday and Sunday in an entire year, (with variations on when a month starts and ends) like so,

11/05/2015  18/05/2015  25/05/2015  01/06/2015  08/06/2015
17/05/2015  24/05/2015  31/05/2015  07/06/2015  14/06/2015

However these are in the date format, and I need them in a text format, but so they still read in the dd/mm/yyyy format, not like 42125.

Further up my document, each column header should read dd/mm/yyyy-dd/mm/yyyy using each of the dates shown in my first example, and I was hoping to achieve this using the formula =A30&"-"&A31 and so on. So the desired outcome should read,

11/05/2015-17/05/2015   18/05/2015-24/05/2015

      11/05/2015            18/05/2015
      17/05/2015            24/05/2015

However using the =cell&cell formula im left with

42135-42141 42142-42148

11/05/2015  18/05/2015
17/05/2015  24/05/2015

I have to create these headings for 2 years worth of dates, and trying to avoid typing every heading out manually, is there a way to achieve this?

Upvotes: 1

Views: 637

Answers (4)

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

Assuming headings in row 1 and dates in rows 3 and 4 this will work:

=TEXT(A3,"dd/mm/yyyy")&TEXT(A4," - dd/mm/yyyy")

without having to concatenate " - "

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

Use the TEXT function which comes with Excel.

Assuming cell A1 has "11/05/2015", use the following formula in cell B1:

B1=TEXT(A1,"dd/mm/yyyy")

This takes care of the first part of your question. For the second part, you can use the CONCATENATE function. Assuming that B1 and B2 contain dates as text, you can join them together using the following formula:

=CONCATENATE(B1, "-", B2)

Upvotes: 3

ttaaoossuu
ttaaoossuu

Reputation: 7894

Try converting those values to text before concatenating:

=TEXT(A1,"dd/mm/yyyy")&"-"&TEXT(A2,"dd/mm/yyyy")

Upvotes: 2

dimitris_ps
dimitris_ps

Reputation: 5951

You need to break them down like so:

=DAY(A3)&"/"&MONTH(A3)&"/"&YEAR(A3)&"-"&DAY(A4)&"/"&MONTH(A4)&"/"&YEAR(A4)

I am assuming here your data start from cell A3

Upvotes: 2

Related Questions