cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Extract Month of a Date Field

Hey, guys, what's up?
 
I have a form, which has an answer date as one of its output fields, in the following format: "6/3/20 11:45:17".
 
I'd like to extract only the month of that date. I was doing it the way below but it's definitely not working.
 
222.PNG
 
 
 
 
 
 
 
 
 
The WDL expression that I'm using is: formatDateTime(body('Obter_os_detalhes_da_resposta')?['submitDate'])?['MMMM']
 
Thanks!! 
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Dual Super User
Dual Super User

Re: Extract Month of a Date Field

Hi again!


@Rebecca7 wrote:
Hello my friend!
 
That expression worked! (first(split('6/3/20 11:45:17','/')))
 
But I was thinking better... is it possible to turn this date into "June 2020"?

 

If you need complex transformations, you will probably need to transform your source date format into ISO 8601 format, that is the one internally supported by Power Automate.

 

 

 

 

yyyy-MM-ddTHH:mm:ss.fffffffZ

 

 

 

 

In order to get the year as 'yyyy' you can add an 'Initialize variable' action block, type string, lets call it inputYear, and assign as its value the following expression:

 

 

 

 

concat('20',first(split(last(split('6/3/20 11:45:17','/')),' ')))

 

 

 

 

In order to get the month as 'MM' you can add an 'Initialize variable' action block, type string, lets call it inputMonth, and assign as its value the following expression :

 

 

 

 

if(equals(length(first(split('6/3/20 11:45:17','/'))),2),first(split('6/3/20 11:45:17','/')),concat('0',first(split('6/3/20 11:45:17','/'))))

 

 

 

 

In order to get the day as 'dd' you can add an 'Initialize variable' action block, type string, lets call it inputDay, and assign as its value the following expression:

 

 

 

 

if(equals(length(split('6/3/20 11:45:17','/')[1]),2),split('6/3/20 11:45:17','/')[1],concat('0',split('6/3/20 11:45:17','/')[1]))

 

 

 

 

 

In order to get everything into ISO 8601 you can use the following expression

 

 

 

 

concat(variables('inputYear'),'-',variables('inputMonth'),'-',variables('inputDay'),'T00:00:00.000000Z')

 

 

 

 

 

Now you can use formatDateTime() to transform it into June 2020.

 

 

 

 

formatDateTime(concat(variables('inputYear'),'-',variables('inputMonth'),'-',variables('inputDay'),'T00:00:00:000000K'),'MMMM yyyy')

 

 

 

 

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Highlighted
Helper I
Helper I

Re: Extract Month of a Date Field

I tried "2020-06-03T00:00:00" and worked!!

 

Thank you for your help and your time once again!!

View solution in original post

11 REPLIES 11
Highlighted
Dual Super User
Dual Super User

Re: Extract Month of a Date Field

Hi!

If the output from your form response is the following, I am afraid formatDateTime() is useless:

6/3/20 11:45:17

 

Assuming in this example month is nr 6 you can try with the following expression instead:

first(split('6/3/20 11:45:17','/'))

 

But if in your example month is nr 3 instead you can try ith the following WDL expression instead:

split('6/3/20 11:45:17','/')[1]

 

You just need to replace '6/3/20 11:45:17' in the expressions and add the corresponding dynamic content instead

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Helper I
Helper I

Re: Extract Month of a Date Field

Hello my friend!
 
That expression worked! (first(split('6/3/20 11:45:17','/')))
 
But I was thinking better... is it possible to turn this date into "June 2020"?
Highlighted
Post Prodigy
Post Prodigy

Re: Extract Month of a Date Field

Hi @Rebecca7 ,

 

FormatDatetimevalue will return you the month 

 

I used for current date time value in compose action with three different formats

formatDateTime(utcNow(),'MM'
formatDateTime(utcNow(),'MMM')
formatDateTime(utcNow(),'MMMM')
 
and following is the output I am getting 
 
1.JPG
Highlighted
Dual Super User
Dual Super User

Re: Extract Month of a Date Field

Hi again!


@Rebecca7 wrote:
Hello my friend!
 
That expression worked! (first(split('6/3/20 11:45:17','/')))
 
But I was thinking better... is it possible to turn this date into "June 2020"?

 

If you need complex transformations, you will probably need to transform your source date format into ISO 8601 format, that is the one internally supported by Power Automate.

 

 

 

 

yyyy-MM-ddTHH:mm:ss.fffffffZ

 

 

 

 

In order to get the year as 'yyyy' you can add an 'Initialize variable' action block, type string, lets call it inputYear, and assign as its value the following expression:

 

 

 

 

concat('20',first(split(last(split('6/3/20 11:45:17','/')),' ')))

 

 

 

 

In order to get the month as 'MM' you can add an 'Initialize variable' action block, type string, lets call it inputMonth, and assign as its value the following expression :

 

 

 

 

if(equals(length(first(split('6/3/20 11:45:17','/'))),2),first(split('6/3/20 11:45:17','/')),concat('0',first(split('6/3/20 11:45:17','/'))))

 

 

 

 

In order to get the day as 'dd' you can add an 'Initialize variable' action block, type string, lets call it inputDay, and assign as its value the following expression:

 

 

 

 

if(equals(length(split('6/3/20 11:45:17','/')[1]),2),split('6/3/20 11:45:17','/')[1],concat('0',split('6/3/20 11:45:17','/')[1]))

 

 

 

 

 

In order to get everything into ISO 8601 you can use the following expression

 

 

 

 

concat(variables('inputYear'),'-',variables('inputMonth'),'-',variables('inputDay'),'T00:00:00.000000Z')

 

 

 

 

 

Now you can use formatDateTime() to transform it into June 2020.

 

 

 

 

formatDateTime(concat(variables('inputYear'),'-',variables('inputMonth'),'-',variables('inputDay'),'T00:00:00:000000K'),'MMMM yyyy')

 

 

 

 

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Highlighted
Helper I
Helper I

Re: Extract Month of a Date Field

Hello @efialttes !

 

I'm following your instruction but the expression bellow is not working:

 

if(equals(length(split('6/3/20 11:45:17','/')[1]),2),split('6/3/20 11:45:17','/'[1]),concat('0',split('6/3/20 11:45:17','/')[1]))

 

Thanks!

 

Highlighted
Dual Super User
Dual Super User

Re: Extract Month of a Date Field

Oops!

if(equals(length(split('6/3/20 11:45:17','/')[1]),2),split('6/3/20 11:45:17','/')[1],concat('0',split('6/3/20 11:45:17','/')[1]))


Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Helper I
Helper I

Re: Extract Month of a Date Field

It worked!

 

But now I'm Facing this problem: 

 

InvalidTemplate. Unable to process template language expressions in action 'Adicionar_linhas_a_um_conjunto_de_dados' inputs at line '1' and column '2866': 'In function 'formatDateTime', the value provided for date time string '2020-06-03T00:00:00:000000K' was not valid. The datetime string must match ISO 8601 format.'.

 

I can't understand... "2020-06-03T00:00:00:000000K" isn't already in the ISO format?

Highlighted
Dual Super User
Dual Super User

Re: Extract Month of a Date Field

Hi!

Try with this instead

2020-06-03T00:00:00:000000Z



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Dual Super User
Dual Super User

Re: Extract Month of a Date Field

Hi!

Try with this instead

2020-06-03T00:00:00.000000Z



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Helper I
Helper I

Re: Extract Month of a Date Field

I tried "2020-06-03T00:00:00" and worked!!

 

Thank you for your help and your time once again!!

View solution in original post

Highlighted
Dual Super User
Dual Super User

Re: Extract Month of a Date Field

@Rebecca7 

I've seen you have marked this topic as "Solved" by clicking "Accept as a solution". Thanks for your kindness! This way others with the same problem can find a solution faster. Please also I have marked your last response as solution since you provided essential details to make it work.

 

Thanx for your help making this community great!

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Helpful resources

Announcements
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (8,364)