Simple question that I have not been able to find an answer to.
I have some data that doesn't have years and is formatted as Mar-29, as I have pasted into Excel, but Excel converts this into data like this 1/3/2029.
How can I make this into a usable date format, making all dates for 2020?
Output should be DD/MM/YYYY.
36 Answers
Format the column that receives the pasted 'dates' as Text before pasting the information in. This will allow them to retain the Mar-29 as text.
Once the text-that-looks-like-a-date values have been pasted in, select the column and run Data, Data Tools, Text to Columns. On the third Text to Columns dialogue page use Date, MDY and click Finish. This will add 2020 as the year for each of the dates.
While the column is still selected, apply the desired date format (Ctrl+1).
If you want to rework the dates after they have been erroneously pasted in then try this formula,
=DATE(2020, MONTH(A2), TEXT(A2,"yy"))With data in A1, in B1 enter:
=IF(ISNUMBER(A1),A1,DATEVALUE(LEFT(A1,3) &" " & MID(A1,5,2)))and format B1 as you like:
You need to paste the value into a column formatted as text, then use this formula changing B9 to your target cell:
=DATEVALUE(RIGHT(B9,LEN(B9)-FIND("-",B9,1))&"-"&LEFT(B9,FIND("-",B9,1)-1)&"-2020")
you can then copy and paste the value as a date.
You can use this formula:=TEXT(DATEVALUE("29/03/2020"),"dd/mm/yyyy")
All of the other answers so far, except for Jeeped’s, seem to assume that you have the text “Mar-29” in your spreadsheet. If that’s true, then this answer might not be useful to you. (Or maybe you can adapt it to your situation.) My understanding of the question is:
- You have the text “Mar-29” somewhere.
- You paste it into Excel.
- Excel interprets it as
mmm-yyand converts it to the date value Thursday, March 01, 2029, … - … and displays that value as “1/3/2029” using the
d/m/yyyyformat
(which is the preferred numeric date format in the United Kingdom). - You want it to be interpreted
as Sunday, March 29, 2020 (
mmm-dd+ current year)
and displayed as 29/03/2020 (DD/MM/YYYY).
Fixing the pasting process, so you never get wrong data in your spreadsheet
(as Jeeped’s answer tries to do), may be the best approach.
But, once you have the 2029, etc., dates in your spreadsheet (say, in A1),
you can fix them by putting
=DATE(2020, MONTH(A1), YEAR(A1)-2000)into B1 (or wherever you want).
This simply builds a date value with
- year=2020,
- month=the month value of the date value in
A1(March, the 3rd month), and - day=the year value of the date value in
A1(2029) minus 2000, to get the rightmost two digits.
Of course, if you have more values in Column A,
you can drag that formula down Column B.
To apply the fix permanently, copy and paste values.
I assume that you know how to change the display format for dates.
Notes:
- This may produce bad results if
A1does not contain a date value that’s corrupted in the way you’ve described (garbage in, garbage out). You might want to add validation. The above formula hard-codes the year 2020. If you want a formula that uses the current year, use
=DATE(YEAR(TODAY()), MONTH(A1), YEAR(A1)-2000)- I’m not sure why you would want to,
but you could use
MOD(YEAR(A1), 100)instead ofYEAR(A1)-2000for the day-of-the-month inB1.
You can do this with a formula too. When the date is in A2, use:
=CONCATENATE(RIGHT(A2, 2),"-",LEFT(A2,3),"-","2020")
This assumes the format is always 2 characters for the day and 3 for the month. If it's sometimes 1 character for the day, you could use MID to look part way into the string, instead of RIGHT.
2
