If you are using the Excel Online Business connector in your Power Automate to get a table and that table has a column of Date, you will notice that the connector actually converts the date into an integer. Hence this doesn’t remain as a Data Type and it could give you an error for mis matched data types for future actions.
So why is it doing so, apparently the Flow Connector count the number of days between 1900-1-1 and your Date value and display that as an Integer.
So we will use a simple formula to get this number back. The formula is for AddDays (Reference Documentations)
addDays('<timestamp>', <days>, '<format>'?)So in our above example, we will simply use a function to Get the Value converted from 1900-1-1.
So in our Flow we will add a dynamics expression
addDays('1899-12-30',int(items('Apply_to_each')?['DueDate']),'yyyy-MM-dd')Scenario:
Creating Tasks from Excel Online
So in this example i will be using a excel file to create tasks in my Office365 Planner.
So first i get the Table from Excel file using my List Rows in a Table Connector
Since it could return a table of data i will use a For Each condition to get value of each Row. And within their each Row i will add the action to create a task and i will use my custom expression in the Due Date Time column that i just described above
addDays('1899-12-30',int(items('Apply_to_each')?['DueDate']),'yyyy-MM-dd')
Now Everything seems good. Since because i am using a custom expression i need to make sure that the expression is always valid. So i will add a condition before Creating a task. I will check if the DueDate is empty or not.
empty(items('Apply_to_each')?['DueDate'])
So if the value is not empty i will create a Task and will update my original file with a Task ID
Lets test this out now.
As you can see out of 8 only 1 condition is true because i only had 1 row in my table that was filled.
So i hope you enjoyed this article. Please feel free to let me know if you have concerns or comments.
Comments