Extract year from date data

[MiltonM]

I've been trying to use Date.Year to extract yyyy from mm/dd/yyyy, for a few days now. The best thought I have is to convert the date to text and take the last four characters, but I believe there's a better/easier way to solve this.

Thank you.

[wdx223_Daniel · Accepted solution]

the first parameter of this function should be text, not a date value.

if [order date] is date type,try Text.AfterDelimiter(Text.From([ORDER DATE]),"/",{0,1})

[v-eqin-msft · Accepted solution]

Hi @MiltonM ,

If the column type is Date, please directly go to Add Column tab--> Date-->Year to extract :

If the column type is Text, then go to Add Column tab--> Extract --> Last characters:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MiltonM]

Thank you, Daniel.

I'm painfully new but begging to catch on.

[v-eqin-msft]

Hi @MiltonM ,

Sorry for my miss for your solution posted before.

Please kindly Accept it as the solution. More people will benefit from it.😀

Best Regards,
Eyelyn Qin

[v-eqin-msft · Accepted solution]

Hi @MiltonM ,

If the column type is Date, please directly go to Add Column tab--> Date-->Year to extract :

If the column type is Text, then go to Add Column tab--> Extract --> Last characters:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MiltonM]

Oh, WOW! I've been making it way too hard all this time.

1) select data column

2) Date icon, select year, select year. DONE!

I see now there are many ways to get the result we're seeking, but thanfully this was a "built-in" option.

Thanks to your both. I love this group and shall return.

[Anonymous · Accepted solution]

It knows how to parse dates using the rules of the system's culture.

= Table.TransformColumnTypes(Source,{{"Text", type date}}, "en-US")
// verses
= Table.TransformColumnTypes(Source,{{"Text", type date}}, "en-GB")
// or
= Table.TransformColumnTypes(Source,{{"Text", type date}}, "fr")

// your default is
= Culture.Current

[smpa01]

@MiltonM  Split on right most delimiter, which will be the YEAR

[MiltonM]

That sounds logical; however, I am pretty new and don't know how to split or determine the rightmost delimiter. Of I go to search, the game is afoot.

[wdx223_Daniel]

Text.AfterDelimiter(text,"/",{0,1})

[MiltonM]

Daniel,

I just tried =Text.AfterDelimiter([ORDER DATE],"/",{0,1}), it failed.

[wdx223_Daniel · Accepted solution]

the first parameter of this function should be text, not a date value.

if [order date] is date type,try Text.AfterDelimiter(Text.From([ORDER DATE]),"/",{0,1})