Need to Split Days, Month & Year from Date

[jawaharprm]

Hi All,

i have a issues with converting in days, month and year in separate columns details are given in attachment pls help on same.



Thanks
Jawahar

 

[GraH - Guido]

So the system date format is different from your local settings.

Either go with a Power Query Solution: here you have an option to convert the date using the local format (in this case this is the source system format) and convert it to your local settings. It comes in with clickable buttons to extract the day, month and year from the given date.

In Excel you can do this, select the date column, then on the data ribbon:


next
next


Finish.

Your formulas work.

 

[Belleke]

=DAY(A2)
=MONTH(A2)
=YEAR(A2)

 

[vletm]

jawaharprm​

Do You have a clear idea that are those August or December dates?
... as well as why some of A-column values are number and some values are text?

 

[jawaharprm]

So the system date format is different from your local settings.

Either go with a Power Query Solution: here you have an option to convert the date using the local format (in this case this is the source system format) and convert it to your local settings. It comes in with clickable buttons to extract the day, month and year from the given date.

In Excel you can do this, select the date column, then on the data ribbon:
View attachment 85981

next
next
View attachment 85982

Finish.

Your formulas work.

Hi Sir, Text to columns are not worked but i converted using Power Query thanks for Idea...

 

[jawaharprm]

jawaharprm​

Do You have a clear idea that are those August or December dates?
... as well as why some of A-column values are number and some values are text?

Hi sir, Actually my system date formate is mm/dd/yyyy hence some date are changed as text values, i completed in Power Query, Thanks for your Time..

 

[vletm]

jawaharprm​

I read from Your #6 reply that
... You don't know that are those August or December dates
... and for some 'hence' some are dates and some are texts.
If Your sample file's data is raw data then
It would be better to find out ... why this happens? ... and to get valid data
instead to do same 'manipulation' every time.
Or
How did You import that data?
... could it import with useful dates too?

 

[GraH - Guido]

Hi Sir, Text to columns are not worked but i converted using Power Query thanks for Idea...

In the text to columns, you did choose the date format MDY?
Glad to read you got it working with PQ.

 

[jawaharprm]

jawaharprm​

I read from Your #6 reply that
... You don't know that are those August or December dates
... and for some 'hence' some are dates and some are texts.
If Your sample file's data is raw data then
It would be better to find out ... why this happens? ... and to get valid data
instead to do same 'manipulation' every time.
Or
How did You import that data?
... could it import with useful dates too?

Hi sir,
her i enclosed raw data

 

[p45cal]

her i enclosed raw data

Although you think this is raw data, it's not. The mere fact of you getting it into Excel means that it isn't; Excel has tried to be helpful by converting text to real dates, and what it thinks are days and months depends on the locale that Excel is set to.
If the textual dates are not the same as the locale that Excel is set to then Excel changes what it can, getting the months and days of the month wrong, and those that it can't convert (because it looks to Excel that the date, say, has a 14th month) then it gives up and leaves it as text. This means that it's very likely that any 'conversions' to dates are ALL wrong (except for when the month and the day of the month are the same - as is the case in cells B3:B4 of tyour 'raw' data sheet).
So instead of bringing the data into Excel and later processing it with Power Query, you should bring the data into Excel using power Query.

I don't know where your data comes from (do you cut and paste? open a .txt file, a .csv file?) and it's this which determines the best course to follow when bringing this data into Excel.
Traditionally, if it's cut and paste, the solution is to start by formatting the cells which receive these data as Text before pasting. This is a nuisance because you don't always know how big a range of cells need to be set as text before you import it.

More recently, newer versions of Excel seem to allow you some options on automatic conversions as data is brought in, but it doesn't seem to be enough:

If you disable the highlighted option above, and hover over the information symbol (circled) you're told:

and with me, this is true since Excel does try to convert your date strings.

So the question is, where is this data before it hits Excel?, and if it's a text or csv file or some such, attach it here and we'll be able to show you possibilities to import the data correctly.

 

[vletm]

jawaharprm​

About Your #9 reply and
as written in #10.
If it could be a real raw data there should be clear logic with ... data.
Now, there are differences.
With that kind of data, You'll have a lot of extra work to make sure - what is correct? ... what should modify?
... of course, if You can skip all 'dates' then maybe it could be useful?