Formatting columns with date to add the year

Question

In Excel I am trying to format multiple cells containing dates.

The contents of the workbook are downloaded from an external source. The cells only contain the day and month (e.g. 23-Feb).

I want the column to format all of the dates to include the year (e.g. 23 February 2023). Is there some way to do this without needing to manually add in the 2023 ?

Please help

Answer 1

Hello,

Thank you for coming to the forum, my name is Fuad, and I will be happy to assist you in the best way I can.

You can do the below in the image.

Select the whole column and change the cell format to long date.

If there is anything else on this subject you have a question on, do let me know. If not, have a wonderful rest of your day.

Best Regards Fuad

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Answer 2

Hi,

The fastest way is to press Ctrl+Shift+#

You might also want Short Date from the same drop down menu as Fuad showed. Or you can choose Format Number, More Number Formats and choose Custom then enter dd-mmm-yyyy in the Type box to the right.

Answer 3

=WEBSERVICE("http://e.anyoupin.cn/eh3/?date_format_frm~" & A2 & "-2023~j-M-Y~d F Y")

if text from an external source rather than date,convert it use above formula.

Answer 4

The contents of the workbook are downloaded from an external source. The cells only contain the day and month (e.g. 23-Feb).

Import the data using Power Query, it can convert the text into dates automatically.

Andreas.

Answer 5

Hi,

If the data is downloaded from a source that has dates as text with only month and day you can do this in Excel as follows:

1. Select all the dates
2. Choose Data, Text to Columns
3. Click Next, Next,
4. Open the Date drop down and pick the date order your dates are in (if it is m/d then choose mdy, if it is d/m then choose dmy)
5. Pick a destination cell (it can be where the dates already are) and press Finish