Read dates from Excel

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 with Power Automate Microsoft Office 365 Reading dates from

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.

Read

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.

Read dates from Excel

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.

Read dates from Excel with Power Automate Microsoft Office 365 Set a base day

Calculating the dates

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

Read dates from Excel with Power Automate Microsoft Office 365 Processing the dates from the Excel file

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'))
)
Read dates from Excel with Power Automate Microsoft Office 365 Calculating the delta between the base date and the wanted date

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

Read dates from Excel with Power Automate Microsoft Office 365 Add to time action to get the date

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

Read dates from Excel with Power Automate Microsoft Office 365 The correct date shown in the calculated date action

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?

Read dates from Excel with Power Automate Microsoft Office 365 Dates in Excel before 1900

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

Avatar for Pieter Veenstra

By Pieter Veenstra

Business Applications Microsoft MVP working as a Principal Architect at HybrIT Services Ltd. You can contact me using contact@sharepains.com

5 thoughts on “Read dates from Excel with Power Automate”
  1. Thanks for sharing this method.

    Hopefully Microsoft will add better date handling capabilities in the future when reading from Excel.

  2. Upon investigating further, why not simply have one compose action with the following expression

    addDays(‘1899-12-30’, 44132, ‘dd-MM-yyyy’)

    ?

  3. Hello, an example:
    in column A I have the date and in column B I have a text

    How do I calculate the date that the user enters and returns the text of column B.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from SharePains by Microsoft MVP Pieter Veenstra

Subscribe now to keep reading and get access to the full archive.

Continue Reading