0

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.

  • Is a date from the first nine days of the month formatted as Mar-01 or Mar-1? – Mike Scott Mar 29 at 7:21
  • (1) What locale are you in?  I.e., what country/language, and how are dates formatted by default? (2) What happens if you manually type “Mar-29” into a cell?  (3) What happens if you enter “Mar-32”? – Scott Mar 29 at 20:52
  • earlier dates are Mar-01. locale is british. the same thing happens if I type Mar-29 i.e. "1/3/2029" Mar-32 results in "1/3/1932" – jakethedog Mar 30 at 0:30
1

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"))
| improve this answer | |
  • 1
    I don’t know whether this is right — I can’t test it, because I can’t reproduce the problem — but I believe that all the others are wrong. – Scott Mar 29 at 20:47
  • 1
    I think you are on the right track since you at least understood the problem of excel mangling the dates. I tried to format the whole sheet as text before pasting the table (copied from a website) but it did not help. The dates are still mangled upon pasting. – jakethedog Mar 29 at 23:33
  • FWIW, I was able to reproduce the paste problem and this worked on my end. If your problem of pasting no longer produces the 1/3/2029 dates then perhaps just remember this for next time. – user385793 Mar 29 at 23:36
0

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:

enter image description here

| improve this answer | |
0

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.

| improve this answer | |
0

You can use this formula:
=TEXT(DATEVALUE("29/03/2020"),"dd/mm/yyyy")

| improve this answer | |
  • Where do you get 01/03/2020?  You may need to read the question again, more carefully. – Scott Mar 29 at 15:04
  • I didn't think something this obvious would need to be explicitly declared, but you should change the date value to whatever date you are trying to output; my answer was mostly intended to provide a purely formulaec methodology, which has advantages in speed-/ease-of-input, etc. But just to humor you, I've edited in OP's given date, so now it's copy-paste-ready. – Arctiic Mar 30 at 19:38
  • Also, note that despite the misleading formula name (as well as Microsoft help resource), the =TEXT() formula does not convert the cell data format to text string. Those of you who assumed this should test it for yourselves. – Arctiic Mar 30 at 19:49
  • (1) So you’re saying, if the OP has damaged dates in A1:A100, they should type your formula into each cell B1, B2, …, B100, each time manually copying the value displayed in the corresponding cell in Column A?  (2) My point is that you used an example value that is similar to the value in the question, but not the same.  This makes me wonder how carefully you read the question.  … (Cont’d) – Scott Mar 30 at 21:16
  • (Cont’d) …  (3) Your edit actually made it worse.  I’ll admit, I skimmed your comment before I started to write this comment.  Upon reading your comment carefully (yeah, ironic), I see that you’re saying: ‘‘For each cell, manually determine what value you want it to have, and then type that value.’’ – Scott Mar 30 at 21:16
0

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-yy and converts it to the date value Thursday, March 01, 2029, …
  • … and displays that value as “1/3/2029” using the d/m/yyyy format
    (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 A1 does 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 of YEAR(A1)-2000 for the day-of-the-month in B1.
| improve this answer | |
-1

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.

| improve this answer | |
  • no, date should be stored as date, not as text. The output format would be set as desired in the properties – phuclv Mar 29 at 10:52
  • Wrap the whole thing in DATEVALUE and you're getting close to a solution. – user385793 Mar 29 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.