Effortlessly Format Dates & Get Specific Days: Power Automate Solution
HTML-код
- Опубликовано: 27 май 2024
- Are you struggling to format a date or get a particular day of the month in Power Automate? Look no further! In this video, I'll show you how to easily calculate the first day of a month, the last day of the month, the nth day of a month, add a particular time to your date, retrieve the next Friday (or particular day) of the week, all Fridays in that month, the 1st and 3rd Friday of a month or the nth working day of the Month.
I'll take you step-by-step through the process and show you how to avoid common errors such as the ISO8601 error when trying to convert a date string to a datetime or converting from UTC to your local region. Plus, I'll provide examples and a downloadable flow for you to use. This is a great solution for those who want to automate the process of formatting dates and increase productivity.
Don't miss out on this opportunity to learn how to format a date and get a particular day of the month in Power Automate. Hit that like and subscribe button and let's get started!
Also learn how to get the number of working days, minus public holidays in Power Automate or Power Apps - here • Calculating Working Da...
00:00 Intro
00:37 ISO 8601 Error Explanation
03:23 Format Current Date Time
05:30 Convert Timezone UTC to Hawaiian
07:11 Start of the Month
08:10 Nth Day of the Month
08:58 Last Day of the Month
10:17 Specific Time on a Particular Day
11:38 Wednesday or particular next occurence of day of week
19:00 An array of Days for the month
23:50 Retrieve all Fridays for the month and then 1st and 3rd
26:50 Remove weekends from the Array and get 10th Working Day
29:00 Outro
Please also see www.damobird365.com/formatdat... for further examples.
Download the Flow github.com/DamoBird365/PowerA...
Date Time Expressions docs.microsoft.com/en-us/azur...
Time Zones docs.microsoft.com/en-us/wind...
Community Post expression for next day of week powerusers.microsoft.com/t5/P.... Please buy me a coffee www.buymeacoffee.com/DamoBird365 ☕ 
Наука
I spent days trying to fathom date manipulation as I wanted to have a flow that would get some dates for 'next month' and then use these dates in some emails to highlight a schedule. This tutorial helped me out an awfy lot. All in the mother tongue as well.
Thank you so much for this.
This is one of the best videos I've seen on this. In 30 minutes, I have learned more than a couple hours looking at threads online!
Thank you very much. Please let me know if there are any other ideas I could cover 👍
Thank you, for the full two days, i struggled making simple 'date' for approval attachment, and I surrendered, lucky for me I find it. Thank you
Minor improvement: Many DateTime operations have their own format parameter. Wrapping in formatDateTime is not always needed.
So rather than
formatDateTime(addDays(outputs('ComposeStartOfMonth'), item()), 'dddd')
you can simply do
addDays(outputs('ComposeStartOfMonth'), item(), 'dddd')
Otherwise excellent video! I'm learning so much about Power Automate by following you.
Thanks again, I've referred back to this video on a number of occasions now and it's helped me each time to arrive at my solution. On this occasion I was looking to determine the last Wednesday of the month rather than a set 1st or 3rd. I used your example to get the 'Filter array' and crudely parsed this into another compose with my expression set to: "last(body('Filter_array'))?['Date']". I convert that output to 'dd' and use a condition for 'Current_time' distiled to 'dd' to trigger my condition when the two are equal on my weekly occurrence.
I find it insane that you must go to such lengths in Power Automate to setup routine flows on nth day of the month, so many other applications have these natively.
It's a crime your video hasn't got more exposure, hopefully my repeat visits with YT premium are at least getting some revenue 😁😁😁😁😁😁
Cheers, glad this one has been useful. Feel free to share my channel with others. I’m always looking for ideas if you want to drop me a message 👍
Epic brilliant goodness!!! Learnt a lot about other possibilities for some of the functions that you used. Thanks Damo. 🙏🏾
I am learning new topics every day in power automate. Kudos! Again used the add working days in one of my automatic reporting reminders.
Nice one, thanks Chandrasekar.
Great video, once again. Definitely goes into my list to use for future reference!
Superbly explained - cleared so many doubts
Really helpful thank you for sharing !
This is so easy to understand and helpful. I was able to solve exactly what i needed. Thank you very much
Cheers Sandra, appreciate your kind comments.
Great content! Thank you!!!!
Great video. I was getting an ISO 8601 error and this video really helped. me fix it. Thanks.
Great video, can't explain how much it helped me in a project for work, i need to be able to sort comments separated by week beginning this helped me fully understand
Thanks AJ, really happy to have helped 👍
Thank you @DamoBird365 for this video. How would you proceed to get particular business day in a month (D+10) considering a particular calendar, let's say Deutschland, how can I remove not only weekends, but also public holidays in a simple manner?
If you create an array of dates using range you can then filter out the hols with another array of dates. Finally you could use integer index to get the nth day from the filtered array.
I went to update the community post & make a RUclips short on this and discovered a minor error.
Since the expressions rely on UTC time to calculate the current day of the week number, if your timezone is significantly different from UTC time, then it may calculate as if the current day of the week is tomorrow’s day of the week (your time). So say it was 10PM 07/13 Wednesday your time & 1AM Thursday UTC time and you wanted it to return the date of the current day’s weekday 07/13, then it would automatically return the next week’s date 07/20 for the next Wednesday instead of this Wednesday.
I think the same error applies to both methods you showed.
I’m in the process of editing the community post to upload both a time-zone conversion step fix & a template add-on for people who want to Delay their flow actions until their given day of week,
but there’s also this annoying error that often occurs on the forums when I put action code from the clipboard in a post. It prevents me from editing or updating anything.
I may have to scrap that thread & just re-create everything in a new thread.
You are the best. Brazil appreciated.
Thanks Lucas 👍
Thank you
I actually need to use this now to get the start/end of the month date for some reports I'm automating lol.
However, if you ever decide to do another version of this video or a follow-up video, then you could include how to make a scheduled flow run on a certain day of the month using the trigger conditions.
So to only run on the 1st of the month, set the Recurrence to every day, but then add something like
@equals('01', formatDateTime(utcNow(), 'dd'))
to the trigger condition of the Recurrence trigger action.
I hope I don’t do another version 😂
How do you also remove holidays from the flow? I tried to use your other video that exclude holidays but the select22 I can't figure out how to adjust the range. Any suggestions?
Have you watched
ruclips.net/video/G096DY0a_N8/видео.html It explains everything?
Hello, Thanks for the beautiful video! I need to get clarity for a question from you. I have an Excel file with a couple of columns, kept the file in SharePoint location, and with the help of list rows present in a table operation trying to read data from the Excel file with date format as "ISO 061", the output looks like "Joining Date": "2021-01-18T00:00:00.000Z" but I need only the date and month from above value Expected output - "01-18". How do I need to specify the condition in ''Enter custom Value" to get the desired output
‘MM-dd’ 🤞
Hello there,
Can you please let me know if there is a way to find the “earliest/oldest date” from a excel column?
Try asking on the forum powerusers.microsoft.com/ 👍
What if I need to find the MAX date and not the last date of the month? In some cases we may not have data for the last day of the month and we may have to consider the very last file / file having max date for a specific month. Can you please help?
You could try the sort() expression? Then get the first() or last()?
@@DamoBird365 ...The issue is I have to first create groups based on YYYY-MM and then can do the sorting and select first or last. I am not sure as to how I can achieve these steps. I am not a Pro so finding it difficult to create the flow.
@@shubhabratadey it would be worth sharing on the forum powerusers.microsoft.com/
Very useful video, thanks so much! I have an issue where if the date field in a Form is empty the flow to dataverse errors, any ideas what I should try to fix this?
What’s the error?
@@DamoBird365 It was:
'The 'inputs.parameters' of workflow operation 'Add_a_new_row' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/renewaldate' is required to be of type 'String/date-time'. The runtime value '""' to be converted doesn't have the expected format 'String/date-time'.
Corrected now with an 'if(empty()' statement. Thanks so much though for getting back to me so promptly. Have subscribed and you are now my new 'go to'!!
@karenrees7370 nice one, you could also check out coalesce. It returns the first non null value from a list of values. Thanks for the sub. 👍
@@DamoBird365 Will do thanks! Could you give me an example of how to add this as an expression please. I will need to do this same for a sharepoint list
@@karenrees7370 learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#coalesce 👍 insert your expression first, and the alternative date second.
Hi, Can you help on Counting Days between two dates MS Flow. I have built an automated flow and need help counting days between two dates (i.e. Start Date and End Date)
Tom has good post on how to do this tomriha.com/how-to-calculate-difference-between-two-times-in-power-automate/
Can we use greater and lesser in conditions to compare dates?
Yes, are you experiencing different? You might need to be in iso8601, yyyy-MM-dd. The excel action list rows doesn’t let you filter on dates but filter array action should work.
@@DamoBird365 I think it is the nested if statements that I am botching with lesser/greater. I reverted to using a condition instead
Hi Damo, very informative video.. I am having issues where i am looking to do a bulk upload of Get Rows (SQL) and applying this to a Sharepoint List with the same column names. The error seems to be around 4 x Datetime fields i am trying to pass values on.
(The Error)
The 'inputs.parameters' of workflow operation 'Create_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/absence_from' is required to be of type 'String/date-time'. The runtime value '"28-08-2023"' to be converted doesn't have the expected format 'String/date-time'.
absence_from, being one of the datetime fields within the SQL table.
sql raw data format is 2023-05-29 00:00:00:0000
Im not sure how to format/express this correctly within the flow.
Thanks
Hi there, take a look here ruclips.net/video/ybPWtu1i1j0/видео.html and try out parsedatetime().
Thanks for the great video. It helped me a lot. But how do I get the nth day depending on an entry in a sharepoint list?
Rather than using utcnow(), use the dynamic value for the date from SharePoint when creating the array of dates.
@@DamoBird365 Sorry, didn't get it. I mean, if I take the formula body('Filter_arrayRemoveWeekends')?[9]?['Date'] it takes the 9th day of the array, but how can I substitute the ?[9]? with the field entry in my sharepoint list?
@@Joe_McEntire the select that makes up the dates using range() is all based on today’s date ie utcnow(). I think you want the 9th day after a date on SharePoint? If you change the expressions in the select, utcnow() for the date from SharePoint, you can get the 9th day after your SharePoint date? Or maybe I’ve misunderstood.
@@DamoBird365 utcnow() is fine. Currently, it takes the 9th day from the filter array, because the 9 is hard coded in the formula (body('Filter_arrayRemoveWeekends')?[9]?['Date']). Is there a chance, that it does not take the 9th day but the value stored in the sharepoint list. Every time it can be a different value, so it needs to be variable
Ah, sorry, I thought you wanted the 9th date from a day in future but you want nth date. Presumably the SharePoint value is an int? You can replace the 9 for the dynamic value and maybe even wrap in int().
how can I validate if enddate is less or equal to 31/3/year request created +1?
You could try adddays(concat(formatdatetime(utcnow(),’yyyy’),’-03-31’),365) or something similar.
@@DamoBird365 Works perfect tnx
Hi i want to extract the data from Excel by using date filter like if it is Friday I want Saturday, Sunday and Monday's data if it is other days like Monday I want Tuesday data for tues need Wednesday data like that respectively upto thursday usually need to extract next day data can u have any solution to this
You could use the filter action and adddays() to get the data to/from to filter on. Might be a good one to post on the forum? powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums
@@DamoBird365 ok thanks
Hello Damo, thanks a lot for the great video. My question is this - what if i wanted to extract multiple dates from the whole days of the month array and put it into a separate array. For example, i wanted to extract the 1st, 23rd, 27th and 30th dates from the array and put them together in a separate array, how would i do that? I have tried different techniques but i keep getting errors like "Array elements can only be selected using an integer index". I understand the error but i want to use multiple integers and put them all in one array. Hope my question is not confusing.
You could use a filter. Create a compose with an array of numbers, [1,23,27,30] and add a filter array where your date array is the input and the validation is based on outputs(‘compose’) contains int(formatdatetime(item()?[‘date’],’dd’)), I.e the array of days contains the int of each day. It will only return those from your compose array.
@@DamoBird365 Thanks a lot for this Damien, just as you did for the weekends in the video. Do you have a video that iterates through nested Json Array. that is how you are able to retrieve properties inside an arrays within an object and an arrary. See example below
[
{
"body": [
{
"Name": "August 2022 jason.pdf",
"Size": 83535
},
{
"Name": "August 2022 mark.pdf",
"Size": 83089
}
]
}
]
I would like to extract only this portion
[
{
"Name": "August 2022 jason.pdf",
"Size": 83535
},
{
"Name": "August 2022 mark.pdf",
"Size": 83089
}
]
Based on that structure, you want the body array. It’s the first object. First(outputs(‘nameofactionwiththisarrayinit’))?[‘body’]
@@DamoBird365 Hello Damien, you are really the king of power automate, it worked like a charm....thanks a lot!
how to get the last week? from sunday to saturday?
legend
Can you please help I would like to delete an item from a sharepoint list 6 months after that item is created any flow keeps failing because of the expression
Something like formatdatetime(adddays(utcNow(),-180),'yyyy-MM-dd') and if you were using get items, the filter would be: Created lt '@{formatdatetime(adddays(utcNow(),-180),'yyyy-MM-dd')}'
@@DamoBird365 Thanks a lot I will give that a whirl
Thanks for the great video. How do I get the last element in the array using this expression : body('Filter_array')?[0]?['Date'] I tried replacing [0] with [-1] but dint work!! Kindly help.
You can use last(body(‘filter_array’))?[‘date’]
Thank you 🙏🏽 Is there a way to extract only dates from the filter array ( [{"Date":"04-Jun","Day":"Saturday"},{"Date":"11-Jun","Day":"Saturday"},{"Date":"18-Jun","Day":"Saturday"},{"Date":"25-Jun","Day":"Saturday"}]) output like:
04 Jun
11 Jun
18 Jun
25 Jun
Thanks in advance.
@@kalyanikadiyala3426 formatDateTime(utcnow(),'dd MMM')
I tried but dint work! I need to pass the filter array in place of Utcnow() to get the desired output
You can change the formula in the original select?
Hi Damian. Great educationl series. Thanks.
** I am struggling to get a action to work to add months to a year output please. UK locale.
Extract yyyy from an input from a FORM date,
then add 60 months (or any value from the FORM selection.)
Output required. eg 2020 + 60 months = 2025.
Looking forward to your help please
You could look at add to time docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#addToTime and add 5 year or 60 month and specify format as yyyy.
@@DamoBird365 Thanks Damian. Got it right eventually.
You couldn't let it go could you? :)
Haha. I was soooo chuffed after that effort and then had another community user point out another wee mistake, so I have updated the video on both accounts. Don’t go spotting any more errors though 😂
Now I can try to optimize my flow to get the last working day of the current month ;)
Oh good. I’m glad you didn’t just have to change the video for my one thing. 😅
Hi @DamoBird365 I have a list in share point with date values. If I pass today date if there are no entries in the list then it should fetch the nearest date to todas date. For exaple my list consist of planned down time date. If I post next scheduled date it needs to search the list and needs to give the date closer to today's date
You probably want to filter your list where the date is le to today and then sort your list by date and get the top ie first() date?