Trouble With Date / Time

[Anonymous]

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.

[efialttes]

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

[Anonymous]

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

[efialttes]

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!

[Anonymous]

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.  

[v-bacao-msft]

Hi @Anonymous ,

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#M28826

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

Best Regards,

[efialttes]

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

[Anonymous · Accepted solution]

@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