Read dates from Excel with Power Automate

To read dates from Excel files can be tricky, as Excel only give you the number of days since 1 January 1900.

Dates in Excel

I’ve created myself an Excel table with some dates in it. i will use this example Excel file during the rest of my post.

Read dates from Excel

In general when I read Excel files from SharePoint or OneDrive I would use Microsoft Graph, but for this example I will use the out of the box actions. in both cases Excel will return numbers as we will shortly see.

Using the List rows present in a table action I’m getting all the rows from my excel file. I’m then using a compose action to display the results. this isn’t 100% needed, but it makes debugging just that little bit easier.

Running the above two steps, now gives me tat number that I have been talking about. The 28 October 2020 is day 44132 as you can see below.

Setting a base date

I’m going to set my base date to 2020-10-28, but really you could set this to any date.

Calculating the dates

Now to calculate each date from that number that Excel gives me, I’m using the following steps.

The first step is a Compose action that contains the day from the row in my excel.

In the second compose I’m calculating the number of days since my base day using the following expression, using the sub function.

sub(int(outputs('Date_from_Excel')),
    int(outputs('Base_day_2020_10_28'))
)

Now that we know the number of days since the base date we can calculate the day that we are interested in.

And when we run our flow we will now get the right date returned.

Dates before 1900

Ok, so what happens to dates before 1900.

Well, Excel didn’t exist in 1900.

So, what else would you expect than the actual date being returned?

Sometimes, I just wish that all dates were before 1900.

Pieter Veenstra

Is your business still running on paper trails, sprawling Excel files, or ageing Access databases? There's a better way — and I can show you exactly what it looks like. I'm the Technical Director of Vantage 365, a Microsoft solutions consultancy working with clients across the UK, the Netherlands, and worldwide. For over 30 years I've been turning messy, manual business processes into clean, automated systems that save time, reduce errors, and give teams the visibility they need to make better decisions. SharePains is not just any blog run by a Microsoft MVP. Have you ever used Try-Catch in Power Automate? The original post about Try-Catch in Power Automate can still be found on this site, https://sharepains.com/2018/02/07/try-catch-finally-in-power-automate-flow/ Or have you ever used the Pieter’s method to avoid variables and speed up your flows? https://sharepains.com/2020/03/11/pieters-method-for-advanced-in-flows/ You can contact me using contact@sharepains.com

Pieter Veenstra

Is your business still running on paper trails, sprawling Excel files, or ageing Access databases? There's a better way — and I can show you exactly what it looks like. I'm the Technical Director of Vantage 365, a Microsoft solutions consultancy working with clients across the UK, the Netherlands, and worldwide. For over 30 years I've been turning messy, manual business processes into clean, automated systems that save time, reduce errors, and give teams the visibility they need to make better decisions. SharePains is not just any blog run by a Microsoft MVP. Have you ever used Try-Catch in Power Automate? The original post about Try-Catch in Power Automate can still be found on this site, https://sharepains.com/2018/02/07/try-catch-finally-in-power-automate-flow/ Or have you ever used the Pieter’s method to avoid variables and speed up your flows? https://sharepains.com/2020/03/11/pieters-method-for-advanced-in-flows/ You can contact me using contact@sharepains.com