Converting Excel Dates in Power Automate or Logic Apps

Introduction

If you have spent any time in Excel, you’ve probably seen dates look two different ways:

If a cell is intended to be a date but is not formatted as the date data type, you might get that whole number result instead.

This problem is an easy fix in Excel by formatting the cell, row, or column as date. However, when bringing correctly formatted dates into a Logic App or Power Automate flow, we encounter a similar problem.

This article will show how to convert Excel dates in Logic Apps (or Power Automate). For simplicity’s sake, I will refer to using Logic Apps throughout the article since that is what I used in my example, but the process will apply to either.

Body

For the example in this article, assume that we need to send out daily reminders of who is scheduled for a task that day (maybe a schedule of which one of your children needs to wash the dishes that day!).

First, let’s open a new Excel file and create the schedule:

Next, we need to make this data an Excel table. This step is required due to how the OneDrive connectors work.

Highlight all the data, and select Insert > Table:

Now that the Create Table window pops up, verify that it looks correct and select OK.

We can now see that our data is formatted as a table:

We don’t have to do this step since Excel will give the table a default name (like Table1), but to show that we can, let’s rename the table.

Click anywhere in the table to select it, select Table Design > Table Name, and type in the name of the table. For this example, I used ExampleTable as the name.

If we click the A2 cell to check its data type, we can confirm that it is formatted as date type:

At this point, you might be thinking, “Great! Now we can bring this into Logic Apps and since the dates are formatted correctly, everything will work fine.”

Let’s see if that’s correct.

Note: I saved this Excel to a personal OneDrive for use in this example, but it should apply for a business OneDrive account as well.

Within a newly created Logic App, let’s create a trigger that runs daily at 8:00 AM, and then click the + button underneath it to add a new step.

Now we want to add the step to get the Excel data.

Search for OneDrive in the Actions to find the List rows present in a table action, and select the appropriate option:

Using this action, navigate to the location where we saved the schedule, and select the table name from the drop down:

If we run the Logic App now, let’s see what the output looks like:

It comes through in JSON format as an array of objects, which is expected, but the Date values are all formatted as whole numbers. Even though we formatted it correctly in the Excel file, they still came through as whole numbers in the OneDrive connector. How can we fix this?

Note: my example below will show assigning values to different variables for demonstration purposes. In a real scenario, these steps could be consolidated into one step if you wanted.

First, let’s add a step that stores the array of objects that contain our Excel data in an array variable, using the Initialize variable action:

Next, let’s retrieve the unformatted date value from the first object in the array, which contains the first row of data from our Excel file.

Add a new step with another Initialize variable action to extract the first Date value.

It’s cutoff in the screenshot, but the logic will look like this:

variables('array_of_table_rows')[0]['Date']

The 0 is the index of the array, indicating that we want to retrieve the first object, and the Date key is to retrieve the value for Date in that first object.

However, all this does is give us the whole number date value, so we have one more step where we need to convert it.

Add a new step with another Initialize variable action to convert the first_date_unformatted variable we just created.

The logic will look like this:

addDays('1899-12-30', int(variables('first_date_unformatted')), 'yyyy-MM-dd')

As you can see, in order to convert the whole number date, we are adding the whole number value to the 1899–12–30 date, and then specifying the date format we desire.

Here is what those three Initialize variable actions look like together:

Finally, let’s see what the output looks like when we run this.

The unformatted date:

The formatted date:

That’s it! Now we can convert any date received from Excel tables in OneDrive and use them how we see fit.

I hope this helps you as get more and more proficient in building out Logic Apps and Power Automate Flows.

Thanks for reading!