DATE FORMATING

[cherez]

Hello Forum, Please help.

I imported a large excel data with date format as yy-mmm (eg, 21-Sep). However, Excel is seeing this as 9/21/2022.

How can I format this date so it reflects the data sets I am working with (September 2021) .

Thank you

 

[vletm]

cherez
How did You import?
Could You choose then needed format?
Is Your imported date real date or text?
Could You attach a sample of Your original raw data as well as a sample Excel-file which has some data?

 

[cherez]

cherez
How did You import?
Could You choose then needed format?
Is Your imported date real date or text?
Could You attach a sample of Your original raw data as well as a sample Excel-file which has some data?

Hello, Thank you for responding.
I imported as csv
It's a real date
Sample attached

 

[pecoflyer]

This is what I see in your example :
Month,,,
Last 30 Days,,,
21-Sep,,,
21-Aug,,,
21-Jul,,,

Is this correct? That is, of course, text

 

[cherez]

This is what I see in your example :
Month,,,
Last 30 Days,,,
21-Sep,,,
21-Aug,,,
21-Jul,,,

Is this correct? That is, of course, text

yes. Correct. That's from the raw file. Even when I Converted to date on a new work sheet. I Couldn't figure out how to fix the date to reflect as Sept 2021. rather excel see's it as 21 Sept 2022.

 

[cherez]

Not sure If I am missing something here. The result didn't come out as expected. See attached.

The year I want is September 2021.

 

[p45cal]

You could try importing using the Text Import Wizard, where in Step 3 you get:


then when you click Finish, you get:

where a proper Excel date has gone into the cell (always the 1st of the month) and cell formatted as mmm-yy without you needing to do it.

The only problem is getting to the Text Import Wizard, and that depends on your version of Excel… which is?

 

[cherez]

Here we go.

Size of what to attach has to very light

 

[cherez]

You could try importing using the Text Import Wizard, where in Step 3 you get:
View attachment 78271

then when you click Finish, you get:
View attachment 78272
where a proper Excel date has gone into the cell (always the 1st of the month) and cell formatted as mmm-yy without you needing to do it.

The only problem is getting to the Text Import Wizard, and that depends on your version of Excel… which is?

My Excel Version is 2203. Excel 365

If you don't mind walking me how you were able to format the year as 2021. I used the text wizard but date still appears as 2022.
remember, original source file is "21-Sep" which means Sep 2021 for the data data. But my excel/csv sees "21-Sep" as 2022-09-21

 

[pecoflyer]

If you don't mind walking me how you were able to format the year as 2021. I used the text wizard but date still appears as 2022.

Select your range - Data - text to columns - Next - Next - Click "Date" in the Column date format and select YMD - Finish
(like the pictures p45cal provided)

 

[pecoflyer]

Even when I Converted to date on a new work sheet

This doe not work. Formatting is the way XL shows things to the outside world. Real dates are actually numbers in the background.(1 is 01/01/1900,2 is 02/01/1900, etc..)( Non US formats)
When you have text, it stays text, whatever the formatting

 

[p45cal]

In Office 365 bring the text import wizard back by following instructions here: https://officesmart.wordpress.com/2018/06/18/turn-on-the-legacy-wizards-in-microsoft-excel-365/
Then you can:


The important bit is selecting YMD for the first column of data in step 3 of that wizard.

 

[cherez]

Thank you Guys! Been at work al day!