cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rebecca7
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

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

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
efialttes
Super User
Super User

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!



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"?

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

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!



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!

 

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!



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?

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!



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!



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

 

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

@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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (4,423)