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

Trouble With Date / Time

I have a flow that deletes a sharepoint list then 'tries' to replace it with current data on a daily basis.  However I am having trouble passing in the date fields.  

 

In my excel table the format is a date time field but when the flow runs I get an error:

 

 

{
  "status": 400,
  "message": "String was not recognized as a valid DateTime.\r\nclientRequestId: ae759c4f-69c3-4c24-941a-1f88a0109751\r\nserviceRequestId: ae759c4f-69c3-4c24-941a-1f88a0109751"
}

 

 

 

This is the output:

 

 

{
  "Title": "33762",
  "Item_x0020_Description": "GENTLEMAN JACK W/SHKR & TWL CLUB G3",
  "Size": "1.75L",
  "Pack": "3",
  "Rank": "H",
  "Estates_x0020_Group_x0020_Flag": "0",
  "Order_x0020_Number": "H052617",
  "Order_x0020_Date": "43692",
  "Promise_x0020_Date": "43732",
  "Due_x0020_Date": "43728",
  "Order_x0020_Quantity": "1",
  "Order_x0020_UOM": "CS",
  "Delivery_x0020_Instructions": "PPT - XPO/PTL",
  "Internal_x0020_Text_x0020_1": "",
  "Internal_x0020_Text_x0020_2": "",
  "Internal_x0020_Text_x0020_3": "",
  "Internal_x0020_Text_x0020_4": "",
  "Internal_x0020_Text_x0020_5": "",
  "External_x0020_Text_x0020_1": "Louisville,tn",
  "External_x0020_Text_x0020_2": "on slip sheets",
  "External_x0020_Text_x0020_3": "revised ship point",
  "External_x0020_Text_x0020_4": "",
  "External_x0020_Text_x0020_5": "",
  "Substitute_x0020_Item": "",
  "RunDate": "43861"
}

 

 

 

 You can see the dates are in that converted number format.  How do i ensure they get uploaded as a date field?

 

The data table refreshes daily from an email attachment.  I have that attachment as a data source in my table.  I converted the date field to a Date Time format in the query.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver I
Resolver I

Re: Trouble With Date / Time

@efialttes I figured it out.  Since I'm not going to be doing any calculations the date I just went to the source and changed the data type to text.  Then I changed the SharePoint List column to single line of text and ran the flow again.  It worked and all dates show up as expected in my power app.

 

Thank you

View solution in original post

7 REPLIES 7
Highlighted
Dual Super User III
Dual Super User III

Re: Trouble With Date / Time

Hi!
When you give DateTime format to an excel column, its output in flow in an integer
In the following thread, you will find info about how to proceed
https://powerusers.microsoft.com/t5/Building-Flows/Get-a-row-using-date-column-in-Excel/m-p/458086#M...
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
Resolver I
Resolver I

Re: Trouble With Date / Time

I tried to follow along but I'm a little confused.  I am not trying to search for a specific date.  I just need all the integers converted back to Date Format.  

 

Another question... Can you convert dates in PowerApps?  if so then I do not need to worry about converting the date before it gets uploaded into the SharePoint list

Highlighted
Dual Super User III
Dual Super User III

Re: Trouble With Date / Time

Hi again

For the first question, let's assume the Date coming from Excel in integer format is stored in a string variable called 'myTestingDate'

In such case you can translate it by means of the following expression:

addDays('1900-01-01T00:00:00Z',sub(int(variables('myTestingDate')),2))

The explanation is on the thread I added previously

Happy Flowing!

 

 



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
Resolver I
Resolver I

Re: Trouble With Date / Time

So I should have the compose block in between these to actions?  Also the compose block in your example is searching out a specific date (Feb 12).  I just want the integer to be converted into the date it represents.

 

EDIT:  I tried using the initialize variable right after List rows present in a Table and I get an error as it gets put inside an Apply to each block.  

 

 

 

Convert Date Flow.PNG

Highlighted
Community Support
Community Support

Re: Trouble With Date / Time

 

Hi @rmanthey99 ,

 

Please check this similar thread and see if it helps:

https://powerusers.microsoft.com/t5/Building-Flows/Excel-file-to-list-flow-date-issue/m-p/279258#M28...

Because the Date stored in the Excel table is actually number, we need to restore the number to date by expression.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dual Super User III
Dual Super User III

Re: Trouble With Date / Time

Hi again
I would try the following
addDays('1900-01-01T00:00:00Z',sub(int(item()?['nameOfYourDateExcelColumn']),2))
The best way to make sure which is the internal name used by PA flow to represent your excel date column name is to add a compose inside your ApplytoEach, set as its input the result of selecting Dynamic Content -> List rows present in a table -> Name of your excel date column, and click OK. Once added, hover with the mouse over the expression automatically added.
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
Resolver I
Resolver I

Re: Trouble With Date / Time

@efialttes I figured it out.  Since I'm not going to be doing any calculations the date I just went to the source and changed the data type to text.  Then I changed the SharePoint List column to single line of text and ran the flow again.  It worked and all dates show up as expected in my power app.

 

Thank you

View solution in original post

Helpful resources

Announcements
FirstImage

Microsoft Ignite 2020

Check out the announcement of Power Platform content at Microsoft Ignite!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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