Excel date and text Format

Question

I have an excel sheet in which there is a column of data like the one given below

Last day of this week corresponding to Sunday is 41280
Last day of this week corresponding to Sunday is 41287
Last day of this week corresponding to Sunday is 41294
Last day of this week corresponding to Sunday is 41301
Last day of this week corresponding to Sunday is 41308
Last day of this week corresponding to Sunday is 41315
Last day of this week corresponding to Sunday is 41322

This is a file from our client. But we are expecting the last numeric values to be of date format.

Last day of this week corresponding to Sunday is 1/6/2013

I tried changing the format to date, but it is not accepting

How can i achieve the same

Answer 1

I have a neat function that I use with a date in Excel. If I want text to appear in the same cell as the date, I use the following concatenation function.

What I want to see:   Date of Submission: 10/17/2016

What I type in: ="Date of Submission: "&TEXT(NOW(),"MM/DD/YYYY")

Answer 2

Put this formula in the next column along (formula assumes your text is in column A):

=TEXT(RIGHT(A1, 5), "dd/MM/yyyy")

I've dropped in the UK dateformat, but of course you can go for the dateformat that suits you.

Answer 3

Changing the format of the cell should do the job. So changing from Number or General to Short Date or Long Date works for me.