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
and converts it to the date value Thursday, March 01, 2029, …
- … and displays that value as “1/3/2029” using the
(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 (
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
you can fix them by putting
=DATE(2020, MONTH(A1), YEAR(A1)-2000)
B1 (or wherever you want).
This simply builds a date value with
- 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
you can drag that formula down Column
To apply the fix permanently, copy and paste values.
I assume that you know how to change the display format for dates.
- 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
for the day-of-the-month in