cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Single excel date extraction and formating

Hello,

 

   I am wanting to import the date from the excel file below. I have created an email with the table below, minus the date referred and note column. I want to know how to format the date that shows up in my email as a date. The date referred column will always have the same date for each row. I would like to take this date and include it in the body of my email, without getting a ton of emails based off the number of rows in the excel document. 

date.PNGnoteemail.PNG

flow123.PNGflow1234.PNG

 

Any advice would help. 

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Paulie78
Super User
Super User

Change your expression in the select to:

if
(
  equals(item()?['BK Dismissed Date'], '')
  ,'No Date', 
  addDays('1899-12-30', int(item('')?['BK Dismissed Date']), 'dd-MM-yyyy')
)

I think this is right, might need a tweak, I am away from my proper computer

View solution in original post

22 REPLIES 22
Anonymous
Not applicable

@Anonymous ,

 

Since Excel stores dates as a number, the quickest way to get it in a DateTime format is to use the addDays() expression:AddDaysForDateFormatFromExcel.png

 

The expression should be like:

 

 

addDays('1899-12-31',[insert date from Excel here],'yyyy-MM-dd')

 

 

The reason behind this is that the number that Excel stores for the date is the number of days since January 1, 1900. For some reason, to get the right date, I have to add the Excel date to December 31, 1899. You can figure out which one works for you.

 

My theory is that it has to do with Timezones. Since PA deals with most DateTimes in UTC timezone, at Midnight on January 1, 1900 in UTC, it was technically 7PM on December 31, 1899 in EST (my timezone).

 

I hope this helps.

 

thanks,

Kyle

Anonymous
Not applicable

Thank you. I am constantly going to be updating and changing dates to this table. So what would I put in the "insert date from excel here" part of the expression.

Anonymous
Not applicable

@Anonymous ,

 

you would use Dynamic Content from "List Rows Present in a Table" for the column with the desired date in it.

 

thanks,

Kyle

Anonymous
Not applicable

Thank you for your reply. I do not know what I am doing wrong. I am getting an error with the second compose statement. time.PNGdatetime.jpg

Anonymous
Not applicable

Can you please show a screenshot of the output of the first compose from that failed run? (The one in the Apply to Each loop).

 

PA is saying that that isn't a number, when it is expecting one. It's probably the null (empty) cell in the BK Dismissed Date column that is causing this error.

Anonymous
Not applicable

Ah, yes I I do have a empty cell in the BK Dismissed Date, which is something that may happen in this data. 

 

output1.PNGoutput2.PNGoutput3.PNG

Anonymous
Not applicable

I just noticed that you only have the first Compose in your Apply to Each loop and nothing else. This means that your flow is going to loop through all of the rows in your table, but the output from the Compose action in the loop isn't going to be used anywhere before it is replaced by the value in the next iteration of the loop. Additionally (and this is the real problem) the output of the Compose action inside the loop is not accessible by actions outside of the loop. If you tried using Dynamic Content to point to that Compose, it wouldn't show up. The reason for this is that, outside of the loop, that Compose has no value.

 

Any subsequent actions that require the specific output of the first Compose need to be inside of that loop; basically the rest of your flow through building your array.

 

 

As for the issue of having occasional null values in that date column, you'll probably need to add a condition where you check to see if that field is null, if it is, then do nothing with that date value (or lack of value as the case may be), and if it is not (in the "No" branch), have your Compose with the addDays() expression. Then below the condition (but still inside the loop) have the rest of your array-building actions. Then after the loop runs through all of the rows in your Excel Table and builds your table-array, you can create the HTML table and send the email.

 

Those last couple of steps are not my area of expertise, as I have not yet had much (read:any) experience with arrays and HTML tables. But I am confident in the logic for the Loop and the Condition.

 

Let me know how it goes.

 

thanks,

Kyle

Paulie78
Super User
Super User

As you have said:

The date referred column will always have the same date for each row

You might as well just do a single compose that gets the first entry from the Excel sheet instead of picking it up in every loop with this:

addDays('1899-12-30', int(outputs('List_rows_present_in_a_table')?['body/value'][0]['Date Referred']), 'dd-MM-yyyy')

Then you don't have to worry about empty values(unless it is in the first row), and you don't have to execute it many times over.

ExcelFirstDate.PNG

 

Anonymous
Not applicable

@Paulie78 ,

 

Thank you, I was trying to figure out the best way for @Anonymous  to get that date in a single action without looping through each row.

 

However, as I understand it, the BK dismissed date will not always be the same. So @Anonymous  needs a way to evaluate each value and get it into a usable date format to go into their HTML table.

 

I am glad you're here though, as you came to mind as I was considering how to address any issues @Anonymous may have with the actual building of the table for their email.

Paulie78
Super User
Super User

@Anonymous glad to be here! Thank you 😀

 

You could do something like this in the loop:

 

if
(
  equals(items('Apply_to_each')?['BK Dismissed Date'], '')
  ,'No Date', 
  addDays('1899-12-30', int(items('Apply_to_each')?['BK Dismissed Date']), 'dd-MM-yyyy')
)

 

This returns the date if there is one, if there is not it says "No Date". That should fix the error in the loop.

I also wrote up how the dates work on my blog:

https://www.tachytelic.net/2020/11/convert-excel-dates-power-automate/

 

Anonymous
Not applicable

Thank you all for your help. I have tried multiple ways of building this flow. My problem now is getting the table out of the loop and into the email. 

flow1116.png

@Paulie78 Thank you for your instructions on the single compose to get the single date. That worked 

I dont understand why you have the loop at all, am I missing something simple?

Anonymous
Not applicable

@Paulie78 ,

 

I was thinking that @Anonymous needed to loop through each row as each "BK Dismissed Date" was converted to a date from a number. However, I am now wondering if the conversion expression can be put directly into the "Daily Template Variables" in the "BK Dismissed Date" line.

 

Would that bypass the need for looping?

Anonymous
Not applicable

@Anonymous I did try that and got an error that the action type does need to be 'foreach' for repeat items. I also got the same error message when I tried it as a separate compose as well. 

flow22.png

Paulie78
Super User
Super User

I would simply do this:

sortOutTheDate.PNG

By pumping the output from Excel into a select we can sort out the Date AND specify all of the HTML columns because that will thin out the output from Excel. In my example above the input of the select looked like this for one record:

{
    "@odata.etag": "",
    "ItemInternalId": "2c9242e1-a667-4523-a92c-66b845e5350e",
    "Order Ref": "6075",
    "Value": "11223.16",
    "Date": "44126",
    "Date Referred": "44126",
    "BK Dismissed Date": "44126"
  }

and the output looks like this:

{
    "Order Ref": "6075",
    "Value": "11223.16",
    "Date": "22-10-2020"
  }

So the data is ready to plop straight into a html without even having to build the columns etc.

 

Then that will be a really clean flow with that setup and fast too.

 

Anonymous
Not applicable

@Paulie78 Thank you, I think this would work well and keep the flow clean. I am still learning about Power Automate, I am still getting an error and not understanding why. 

flowbkdate.pngflowbkdismissed.png

Paulie78
Super User
Super User

Before we work the error out, can you just get rid of the Date conversion and put the excel date back in, then test it please? Then if it works, we can figure out what went wrong with the date expression.

Anonymous
Not applicable

Yes, the flow worked when I for rid of the Date conversion.

flowwork.pngflowemail.PNG

Paulie78
Super User
Super User

Ah, I know what it is, you still have the blanks in BK Dismissed right? 

 

Helpful resources

Announcements

Exclusive LIVE Community Event: Power Apps Copilot Coffee Chat with Copilot Studio Product Team

It's time for the SECOND Power Apps Copilot Coffee Chat featuring the Copilot Studio product team, which will be held LIVE on April 3, 2024 at 9:30 AM Pacific Daylight Time (PDT).     This is an incredible opportunity to connect with members of the Copilot Studio product team and ask them anything about Copilot Studio. We'll share our special guests with you shortly--but we want to encourage to mark your calendars now because you will not want to miss the conversation.   This live event will give you the unique opportunity to learn more about Copilot Studio plans, where we’ll focus, and get insight into upcoming features. We’re looking forward to hearing from the community, so bring your questions!   TO GET ACCESS TO THIS EXCLUSIVE AMA: Kudo this post to reserve your spot! Reserve your spot now by kudoing this post.  Reservations will be prioritized on when your kudo for the post comes through, so don't wait! Click that "kudo button" today.   Invitations will be sent on April 2nd.Users posting Kudos after April 2nd at 9AM PDT may not receive an invitation but will be able to view the session online after conclusion of the event. Give your "kudo" today and mark your calendars for April 3, 2024 at 9:30 AM PDT and join us for an engaging and informative session!

Tuesday Tip: Unlocking Community Achievements and Earning Badges

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!     THIS WEEK'S TIP: Unlocking Achievements and Earning BadgesAcross the Communities, you'll see badges on users profile that recognize and reward their engagement and contributions. These badges each signify a different achievement--and all of those achievements are available to any Community member! If you're a seasoned pro or just getting started, you too can earn badges for the great work you do. Check out some details on Community badges below--and find out more in the detailed link at the end of the article!       A Diverse Range of Badges to Collect The badges you can earn in the Community cover a wide array of activities, including: Kudos Received: Acknowledges the number of times a user’s post has been appreciated with a “Kudo.”Kudos Given: Highlights the user’s generosity in recognizing others’ contributions.Topics Created: Tracks the number of discussions initiated by a user.Solutions Provided: Celebrates the instances where a user’s response is marked as the correct solution.Reply: Counts the number of times a user has engaged with community discussions.Blog Contributor: Honors those who contribute valuable content and are invited to write for the community blog.       A Community Evolving Together Badges are not only a great way to recognize outstanding contributions of our amazing Community members--they are also a way to continue fostering a collaborative and supportive environment. As you continue to share your knowledge and assist each other these badges serve as a visual representation of your valuable contributions.   Find out more about badges in these Community Support pages in each Community: All About Community Badges - Power Apps CommunityAll About Community Badges - Power Automate CommunityAll About Community Badges - Copilot Studio CommunityAll About Community Badges - Power Pages Community

Tuesday Tips: Powering Up Your Community Profile

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!   This Week's Tip: Power Up Your Profile!  🚀 It's where every Community member gets their start, and it's essential that you keep it updated! Your Community User Profile is how you're able to get messages, post solutions, ask questions--and as you rank up, it's where your badges will appear and how you'll be known when you start blogging in the Community Blog. Your Community User Profile is how the Community knows you--so it's essential that it works the way you need it to! From changing your username to updating contact information, this Knowledge Base Article is your best resource for powering up your profile.     Password Puzzles? No Problem! Find out how to sync your Azure AD password with your community account, ensuring a seamless sign-in. No separate passwords to remember! Job Jumps & Email Swaps Changed jobs? Got a new email? Fear not! You'll find out how to link your shiny new email to your existing community account, keeping your contributions and connections intact. Username Uncertainties Unraveled Picking the perfect username is crucial--and sometimes the original choice you signed up with doesn't fit as well as you may have thought. There's a quick way to request an update here--but remember, your username is your community identity, so choose wisely. "Need Admin Approval" Warning Window? If you see this error message while using the community, don't worry. A simple process will help you get where you need to go. If you still need assistance, find out how to contact your Community Support team. Whatever you're looking for, when it comes to your profile, the Community Account Support Knowledge Base article is your treasure trove of tips as you navigate the nuances of your Community Profile. It’s the ultimate resource for keeping your digital identity in tip-top shape while engaging with the Power Platform Community. So, dive in and power up your profile today!  💪🚀   Community Account Support | Power Apps Community Account Support | Power AutomateCommunity Account Support | Copilot Studio  Community Account Support | Power Pages

Super User of the Month | Chris Piasecki

In our 2nd installment of this new ongoing feature in the Community, we're thrilled to announce that Chris Piasecki is our Super User of the Month for March 2024. If you've been in the Community for a while, we're sure you've seen a comment or marked one of Chris' helpful tips as a solution--he's been a Super User for SEVEN consecutive seasons!   Since authoring his first reply in April 2020 to his most recent achievement organizing the Canadian Power Platform Summit this month, Chris has helped countless Community members with his insights and expertise. In addition to being a Super User, Chris is also a User Group leader, Microsoft MVP, and a featured speaker at the Microsoft Power Platform Conference. His contributions to the new SUIT program, along with his joyous personality and willingness to jump in and help so many members has made Chris a fixture in the Power Platform Community.   When Chris isn't authoring solutions or organizing events, he's actively leading Piasecki Consulting, specializing in solution architecture, integration, DevOps, and more--helping clients discover how to strategize and implement Microsoft's technology platforms. We are grateful for Chris' insightful help in the Community and look forward to even more amazing milestones as he continues to assist so many with his great tips, solutions--always with a smile and a great sense of humor.You can find Chris in the Community and on LinkedIn. Thanks for being such a SUPER user, Chris! 💪 🌠  

Find Out What Makes Super Users So Super

We know many of you visit the Power Platform Communities to ask questions and receive answers. But do you know that many of our best answers and solutions come from Community members who are super active, helping anyone who needs a little help getting unstuck with Business Applications products? We call these dedicated Community members Super Users because they are the real heroes in the Community, willing to jump in whenever they can to help! Maybe you've encountered them yourself and they've solved some of your biggest questions. Have you ever wondered, "Why?"We interviewed several of our Super Users to understand what drives them to help in the Community--and discover the difference it has made in their lives as well! Take a look in our gallery today: What Motivates a Super User? - Power Platform Community (microsoft.com)

March User Group Update: New Groups and Upcoming Events!

  Welcome to this month’s celebration of our Community User Groups and exciting User Group events. We’re thrilled to introduce some brand-new user groups that have recently joined our vibrant community. Plus, we’ve got a lineup of engaging events you won’t want to miss. Let’s jump right in: New User Groups   Sacramento Power Platform GroupANZ Power Platform COE User GroupPower Platform MongoliaPower Platform User Group OmanPower Platform User Group Delta StateMid Michigan Power Platform Upcoming Events  DUG4MFG - Quarterly Meetup - Microsoft Demand PlanningDate: 19 Mar 2024 | 10:30 AM to 12:30 PM Central America Standard TimeDescription: Dive into the world of manufacturing with a focus on Demand Planning. Learn from industry experts and share your insights. Dynamics User Group HoustonDate: 07 Mar 2024 | 11:00 AM to 01:00 PM Central America Standard TimeDescription: Houston, get ready for an immersive session on Dynamics 365 and the Power Platform. Connect with fellow professionals and expand your knowledge. Reading Dynamics 365 & Power Platform User Group (Q1)Date: 05 Mar 2024 | 06:00 PM to 09:00 PM GMT Standard TimeDescription: Join our virtual meetup for insightful discussions, demos, and community updates. Let’s kick off Q1 with a bang! Leaders, Create Your Events!  Leaders of existing User Groups, don’t forget to create your events within the Community platform. By doing so, you’ll enable us to share them in future posts and newsletters. Let’s spread the word and make these gatherings even more impactful! Stay tuned for more updates, inspiring stories, and collaborative opportunities from and for our Community User Groups.   P.S. Have an event or success story to share? Reach out to us – we’d love to feature you!

Users online (4,351)