Shane Bartholomeusz

Technical Solutions Consultant

Handling Excel Dates in Microsoft Power Automate

Problem

Dealing with dates can be a real pain in the proverbial sometimes … and Microsoft Power Automate (aka Microsoft Flow) is no exception.

I recently had to bulk load some records from an Excel file into SharePoint Online using Microsoft Power Automate … you'd be forgiven to think that this would be a trivial task … WRONG!

Microsoft Flow and Excel Logos

All was going well until … I encountered date fields in my Excel file … then all hell broke lose!

I was puzzled why Dates in my Excel file were processed by Microsoft Power Automate as numbers … what the? What's going on here?

Solution

The root problem is that Microsoft Power Automate reads Date columns as Text. Excel stores dates in Serial Date format which means that a date like ‘2019-01-10' will be read as ‘43475' within Power Automate.

You can read more about how excel handles dates here:
http://www.cpearson.com/excel/datetime.htm.

Therefore we need to convert the Number to a Date using the below expression within our Power Automate workflow

// Format
if(empty(<DATE TO CONVERT>),null,addDays('1899-12-30',int(<DATE TO CONVERT>),'yyyy-MM-dd'))
// Example
if(empty(item()?['Start Date']),null,addDays('1899-12-30',int(item()?['Start Date']),'yyyy-MM-dd'))

Example

In this simple example I'll create a workflow to insert the contents of an Excel spreadsheet into a HTML table.

The flow contains the following steps:
1. Read rows from an Excel Spreadsheet
2. Insert each row into a HTML formatted table

Excel Worksheet Screenshot

I've added the below expression into the ‘FormattedDateCompleted' field of the HTML table.

if(empty(item()?['Timesheet Completed Date']),null,addDays('1899-12-30',int(item()?['Timesheet Completed Date']),'yyyy-MM-dd'))

Here is the output after running the workflow. You can see the original date value and the formatted date value.

Final Thoughts

Well I hope this solution has helped you. If you have any thoughts or alternative solutions feel free to post them in the comments below.

Shane Bartholomeusz

10 Comments

  1. Great Article! Thank a lot for your help!

  2. Hi Shane – I’m just getting into Flow. Where exactly did you make the change? Can it be made at the spreadsheet/table level ?

    • Shane Bartholomeusz

      4th November 2019 at 9:48 pm

      Hi Robert, you’ll need to create the Expression within Microsoft Flow via the popup menu that appears when you click a field/step in your Flow.

      Otherwise, (although I haven’t tested this) you could convert the ‘date’ formatted cell to a ‘text’ type field and it should import without issue.

  3. Hi Shane, Thanks for the tip but how to convert/format date and time?

  4. Shane,

    I thank you profusely for this! You have saved me a lot of head scratching!

    Dan

  5. anybody here that can explain to me how to get this configured in Power Automate ?

  6. Great article! btw, how to create an expression if i have date and time in excel, (Example: 43924.04097)

  7. Sorry but this doesn’t work for me. I just get an error stating ‘The Expression is Invalid’. Any ideas what I need to check.

    • Shane Bartholomeusz

      19th July 2020 at 8:26 am

      Hi John,
      Thanks for pointing that out. I just realised there was a mistake in the expression, which I’ve now corrected.

      I’ve also included an example for reference. Hope that helps.

  8. Great. Thanks. I’ll take a look.

Leave a Reply

© 2020 Shane Bartholomeusz

Theme by Anders NorenUp ↑

%d bloggers like this: