cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rmanthey99 Helper III
Helper III

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
rmanthey99 Helper III
Helper III

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

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!



rmanthey99 Helper III
Helper III

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

Dual Super User
Dual Super User

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!



rmanthey99 Helper III
Helper III

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

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.
Highlighted
Dual Super User
Dual Super User

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!



rmanthey99 Helper III
Helper III

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

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Automate Community!

firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Users online (4,983)