cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eis5187
New Member

Flow datetime error

I had a flow that was running just fine for about a month; it looks at rows in an Excel table and, if the date condition is met, it sends an email. The DateTime Format is set to ISO 8601.

 

eis5187_1-1677079579787.png

 

All of a sudden today I received the error: Unable to process template language expressions for action 'Condition' at line '0' and column '0': 'In function 'formatDateTime', the value provided for date time string '45078' was not valid. The datetime string must match ISO 8601 format.'..

 

No one has changed the date format in the Excel file. So I'm confused as to why I'm having this problem all of a sudden. 

 

Ideas? Thanks!

 

11 REPLIES 11

Do you have any blanks in that date column from excel? Whenever there is a blank it throws off how power automate reads the value.

 

https://powerusers.microsoft.com/t5/Building-Flows/When-Excel-first-row-has-blank-date-all-remaining... 

No, no blank rows in the data. I can see from the outputs it thinks the Event Date column is coming in as 

"Event Date":"45078"

But, I changed it in Excel so it looks like this:

eis5187_0-1677174341569.png

 

 

The value "Event Date":"45078" is the date in a serial format. Excel only uses serial numbers for dates.

 

Did changing the date format of excel to "2023-06-01T00:00:00Z" fix your issue?

No, it did not.

I would go check the Excel sheet where the date is "45078" which should be May 19, 2023. Check the date itself as well as the row above it and see if there is any reason it looks different. Like maybe it was put in as text or has an additional space. 

Next I would clean the dates by adding a new column and getting the dates into it by adding 0, then copy and paste over itself. I do that so that I'm not copying anything like extra formats or something. Then I would retry the flow using the new column.

See if you still get the same error when using the new column.

 

It seems like your flow is working, it only stops working when the Apply to Each reaches that date? Or do all of the Apply to Each fail?

Hmm, I don't have any May 19th options. Closest thing would  be June 1st. There's no way for the dates to be different because a Form is populating that field.

 

eis5187_0-1677691706818.png

I did that and got the same error using the new column. 

 

The rest of the flow is working.

eis5187_1-1677691793814.png

 

 

You are correct. The serial date "45078" is June 1, 2023. I just check in Excel. Previously I used some internet time converter and I must have looked at it incorrectly.

 

So at least we know the issue seems to be at the first date row where it is 6/1/2023.

 

Can you take another screenshot that let's me see the List rows present in the table details. You need to expand the entire step so I can see all of the options down to the Date Time format. Also, I want to see the full file name, so don't blank or scratch it out.

eis5187_0-1677697205791.png

 

The first expression is: 

formatDateTime(items('Apply_to_each')?['Event Date'],'MM/dd/yyyy')

Ok, I have my flow set up exactly like yours and I am not getting that error.

 

There is something going on within the excel file itself as opposed to something incorrect in the flow. Either the excel fields are in some kind of format like text or there is something in there that is making it not appear as a date field to Power Automate when it pulls in the Excel values.

I don't know what it could be, you will have to look at the formats and see if there is something different or how it is getting put into the cell in the first place. Like are there "" quotes around it or something?

 

It could be something with the line breaks. I notice in one example of your excel there is a big gap above the date, and then in another example there is no gap. Why are the row heights different? Is it due to spaces in the date fields? How do you have some dates at the top of the cell and then some dates at the bottom??

 

A good way to test this will be to put a Compose step above the condition, and then insert the Event Date field in there without any formatting. So you can see how it looks to power automate. You would expect the Compose to show the full date time value in IS 8601 format. However, if it is just showing the serial you know it is getting pulled in to PA incorrectly. Also you'll be able to see if it is green or black. If it is green that means it is being read as a number. It should be black like a string.

 

So First Thing:

Put a Compose step above the condition with the Event Date and run the flow and see what it looks like.

Is the result in a IS 8601 format? What color is it?

 

Second Thing: 

You need to correct that condition step, because you will never get a comparable answer. You are trying to compare two different formats of date. You have the Event Date as 'MM/dd/yyyy' and the Yesterday as 'yyyy-MM-dd' and those will never be equal. Both date time formats should be 'MM/dd/yyyy' or 'yyyy-MM-dd'.

 

Try the compose step and let me know what it looks like.

Sorry, the date time formats have been the same when I run it. I'd been testing different formats and the mismatch was an oversight of the example I sent you. Here are the results:

eis5187_0-1677699501575.png

 

Thank you for showing me all of that. I am certain it has to do with the Excel file or formats of the data within the Excel file.

I don't know what it is, I've looked through Advanced options of the Excel files looking for something that would cause this, but nothing stands out.

 

It has to involve how the dates/data is getting put into the cells. So the data is being put into Excel, and Excel is doing some work to make it appear like dates to you visiblily. However, it is not really a date.

 

To test this, you can make a completely new blank excel file. Manually type in some dates. Then put that file in your flow as the source and see if you get the same errors.

 

Lastly I would make a new flow and test with the new manual excel file and see if you still get the errors.

 

Helpful resources

Announcements

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Dear Community Members,   We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd.   During this period, members will not be able to Kudo, Comment, or Reply to any posts.   On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities.   What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

We are excited to announce the Summer of Solutions Challenge!    This challenge is kicking off on Monday, June 17th and will run for (4) weeks.  The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions to as many questions as you can. Answers can be provided in all the communities.    Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”)   - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024   Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods.  You must enter into each weekly Entry Period separately.   How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different.    Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers.  At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing.  Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random.  Individuals will be contacted before the announcement with the opportunity to claim or deny the prize.  Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners.   Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event.   ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE**   Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Community MembersNumber SolutionsSuper UsersNumber Solutions Deenuji 9 @NathanAlvares24  17 @Anil_g  7 @ManishSolanki  13 @eetuRobo  5 @David_MA  10 @VishnuReddy1997  5 @SpongYe  9JhonatanOB19932 (tie) @Nived_Nambiar  8 @maltie  2 (tie)   @PA-Noob  2 (tie)   @LukeMcG  2 (tie)   @tgut03  2 (tie)       Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 2: Community MembersSolutionsSuper UsersSolutionsPower Automate  @Deenuji  12@ManishSolanki 19 @Anil_g  10 @NathanAlvares24  17 @VishnuReddy1997  6 @Expiscornovus  10 @Tjan  5 @Nived_Nambiar  10 @eetuRobo  3 @SudeepGhatakNZ 8     Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 3:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji32ManishSolanki55VishnuReddy199724NathanAlvares2444Anil_g22SudeepGhatakNZ40eetuRobo18Nived_Nambiar28Tjan8David_MA22   Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 4:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji11FLMike31Sayan11ManishSolanki16VishnuReddy199710creativeopinion14Akshansh-Sharma3SudeepGhatakNZ7claudiovc2CFernandes5 misc2Nived_Nambiar5 Usernametwice232rzaneti5 eetuRobo2   Anil_g2   SharonS2  

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.    

Updates to Transitions in the Power Platform Communities

We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for.  We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer.   Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together.   Here's to new beginnings and endless possibilities!   If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport.   To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages

Top Kudoed Authors
Users online (2,605)