Excel - Date Formats

Question

Hi

Help. Despite reading numerous threads on this topic, the date format in my excel spreadsheets are still incorrect.

I have the Office Home and Business 2019 version. The data and spreadsheet are "new", not data transferred or copied over from any other source.

My goal is the format: 3-Feb-22 (DD- MMM-YY)

I have tried the cell format date option from the ribbon - in a hundred different ways. I have tried creating custom cell formats. I have tried numerous versions of text to columns...nothing works. I have checked - the column is not text.

Short of keying it exactly as I want it manually (eg keystrokes of 3-Feb-22), excel can't seem to read a numerical entry. For example if I key 3/2/22 - the result is March or the year 2002 or the day is 22 etc. Since there are many dates to key, it is much simpler to key numbers - yet I can't get the result needed.

Any ideas?

Answer 1

Thank you for this idea.

Yes I have tried it. and this seemed most logical to me as well as I have used in older versions of excel with no problems.

For some reason I still get "22-mar-02", if I key 2/3/22

Answer 2

May I know what Excel is rendering as the result of this formula? A screenshot would help.

Answer 3

Hi

Thank you for the idea. Sadly it didn't work either. Also, this would then need two columns of data - one for the formula and one for the original keying.

Somehow I should be able to simply key the value in the primary cell...and excel pops out the right format.

Answer 4

Hi

Help. Despite reading numerous threads on this topic, the date format in my excel spreadsheets are still incorrect.

I have the Office Home and Business 2019 version. The data and spreadsheet are "new", not data transferred or copied over from any other source.

My goal is the format: 3-Feb-22 (DD- MMM-YY)

I have tried the cell format date option from the ribbon - in a hundred different ways. I have tried creating custom cell formats. I have tried numerous versions of text to columns...nothing works. I have checked - the column is not text.

Short of keying it exactly as I want it manually (eg keystrokes of 3-Feb-22), excel can't seem to read a numerical entry. For example if I key 3/2/22 - the result is March or the year 2002 or the day is 22 etc. Since there are many dates to key, it is much simpler to key numbers - yet I can't get the result needed.

Any ideas?

Have you tried?:-

1.

CTRL + 1

 - to open the window called:-

Format Cells

2.

In the window called:-

Format Cells

 -and in its tab called:-

Number

 - change the format as shown in the image below.

As you can see in the green cell it appears to give you what you want.

Do you agree?

Answer 5

Hi Michelleabc12,

Sorry to hear that you're experiencing this issue!

I am Bhavik, a Microsoft user like you. Please note, I am not a Microsoft employee and don’t have direct access to your account, but I will do everything I can to help you with your queries. :)

Did you try this formula?

=TEXT(H13,"dd-mmm-yy")

Please replace H13 with the cell address where you have your date written as 3/2/22.

Thanks

Bhavik